Excel Date Formatting ex dates like 1795

[Mary Ellis]

I am trying to do a genealogy spreadsheet. I have dates in several different
formats. If I have a date like 1795 and put it in a date formatted field it
will change to something like Nov. 29, 1904.
1750 changes to 10/15/1904
1800 changes to 12/4/1904
1850 will change to 1/23/1905
The only way to make them stay 1750 1800 and 1850 is to enter them as text
by using '1750, '1800. '1850

Is there a fix or addin or something that will allow me to just paste the
data in and be done with it. I lose lots of time checking all dates after
say 1910 to make sure they are right or need to be changed to 1750 etc.

Thanks
Mary Ellis

 

[JoAnn Paules]

Do you have month and day for those entries or just a year?

 

[Bob Buckland ?:-\)]

Hi Mary

Internally (for reasons going back in history to Gregorian calendar origins it seems <g>) Excel sees dates as serial numbered items
with the series and thus its history of the world (as far as dates) beginning in either 1900 or 1904 depending on a setting in Excel
options.

This doesn't mean you can't use Excel for genealogy work, a lot of folks tend to use it, although there are quite a few dedicated
genealogy shareware and commercial software packages available. Excel MVP John Walkenbach has a add-in you can use with Excel
available here:
http://j-walk.com/ss/excel/files/xdate.htm

For help with using it or for other pre-XLhistory date questions you may want to also post in the MS Excel discussion groups using
the link below and include the version of Excel you'll be working with

============
I am trying to do a genealogy spreadsheet. I have dates in several different
formats. If I have a date like 1795 and put it in a date formatted field it
will change to something like Nov. 29, 1904.
1750 changes to 10/15/1904
1800 changes to 12/4/1904
1850 will change to 1/23/1905
The only way to make them stay 1750 1800 and 1850 is to enter them as text
by using '1750, '1800. '1850

Is there a fix or addin or something that will allow me to just paste the
data in and be done with it. I lose lots of time checking all dates after
say 1910 to make sure they are right or need to be changed to 1750 etc.

Thanks
Mary Ellis >>
--
Please let us know if this has helped,

Bob Buckland ?
MS Office System Products MVP

LINKS
A. Specific newsgroup/discussion group mentioned in this message:
news://msnews.microsoft.com/microsoft.public.excel
or via browser:
http://microsoft.com/communities/newsgroups/en-us/?dg=microsoft.public.excel

B. MS Office Community discussion/newsgroups via Web Browser
http://microsoft.com/office/community/en-us/default.mspx
or
Microsoft hosted newsgroups via Outlook Express/newsreader
news://msnews.microsoft.com

 

[Mary Ellis]

Using 2007 - My dates range from just the year to month and year to complete
date. You would think after all these years they would realize the world
didn't start in 1900.

Thanks for answering
Mary

Do you have month and day for those entries or just a year?

--

JoAnn Paules
MVP Microsoft [Publisher]
Tech Editor for "Microsoft Publisher 2007 For Dummies"

I am trying to do a genealogy spreadsheet. I have dates in several
different
formats. If I have a date like 1795 and put it in a date formatted field
it
will change to something like Nov. 29, 1904.
1750 changes to 10/15/1904
1800 changes to 12/4/1904
1850 will change to 1/23/1905
The only way to make them stay 1750 1800 and 1850 is to enter them as text
by using '1750, '1800. '1850

Is there a fix or addin or something that will allow me to just paste the
data in and be done with it. I lose lots of time checking all dates after
say 1910 to make sure they are right or need to be changed to 1750 etc.

Thanks
Mary Ellis

 

[Mary Ellis]

Thanks Bob.
I use 2007, and I also have several Gen programs, I use FamilyTreeMaker for
my primary database. I like to export the file out every once in a while
and sort by dates to see at one glance where I need to put the most work. It
shows me in one excel file where I need to concentrate the most. But when I
have to spend half my time correcting dates it defeats the purpose.

Thanks for the links and the addin.

Mary

 

[JoAnn Paules]

Blame it on the fact that no one who wrote the program is over 29. ;-)

--

JoAnn Paules
MVP Microsoft [Publisher]
Tech Editor for "Microsoft Publisher 2007 For Dummies"

Using 2007 - My dates range from just the year to month and year to
complete
date. You would think after all these years they would realize the world
didn't start in 1900.

Thanks for answering
Mary

Do you have month and day for those entries or just a year?

--

JoAnn Paules
MVP Microsoft [Publisher]
Tech Editor for "Microsoft Publisher 2007 For Dummies"

I am trying to do a genealogy spreadsheet. I have dates in several
different
formats. If I have a date like 1795 and put it in a date formatted
field
it
will change to something like Nov. 29, 1904.
1750 changes to 10/15/1904
1800 changes to 12/4/1904
1850 will change to 1/23/1905
The only way to make them stay 1750 1800 and 1850 is to enter them as
text
by using '1750, '1800. '1850

Is there a fix or addin or something that will allow me to just paste
the
data in and be done with it. I lose lots of time checking all dates
after
say 1910 to make sure they are right or need to be changed to 1750 etc.

Thanks
Mary Ellis

 

[Trish]

Mary - Like yourself I use FTM and also put IGI/Parish Records in a
spreadsheet just to enable me to do quick easy different sorts - especially
when I haven't confirmed whether ancestor or even kinsman. I have recently
purchased and installed Office 2007 and hit the date issue immediately - wish
I had known pre purchase! Anyway I know where you are coming from with year,
year and month, and complete date options so just wanted to check a) whether
you have his sorted now and b) whether the add-in worked for you and how you
implemented it - I am having problems still after 2 days of trying various
options. Can you still use various format options or not please - Trish