How do I get Excel to recognise dates prior to 1/1/1900 ?

[Guest]

As title asks. Family Tree information finds some earlier dates fail to be
recognised and formatted as such.

 

[Jim Rech]

Excel does not recognize pre-1900 dates as other than text. You might check
out this add-in from John Walkenbach which will help:

http://www.j-walk.com/ss/excel/files/xdate.htm

 

[Bernard Liengme]

See http://j-walk.com/ss/excel/usertips/tip028.htm

Always type full question in the message space not the subject line

 

[David McRitchie]

I would recommend that you use text so that they will not change; however,
if you want to risk using a macro John Walkenbach wrote
Extended Dates routines

Excel does not recognize dates before Jan 1, 1900 and mishandles leap year in 1900
so for simply calculating date differences (age) you might use John Walkenbach's
XDATEDIF Extended Date Functions Add-In, instead of DATEDIF, eliminating problems
with negative dates involving subtraction in MS date system and incorrect leap years in
older MS 1900 date system.
http://www.j-walk.com/ss/excel/files/xdate.htm

You calculations can be further complicated by when a country switched calendars.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Gord Dibben]

Gazz

See John Walkenbach's site for working with dates prior to 1900.

Download his Extended Date add-in.

http://search.atomz.com/search/?sp-q=dates+prior+to+1900&sp-a=000336ae-sp00000000


Gord Dibben Excel MVP

 

[Guest]

Excel's normally formatted dates only go back that far. You can use custom
add-ins or just use Julian dates ( without fancy formatting and support
functions.

 

[Guest]

Thanks all. Lots to go on there.

Not expecting dates to change as they're not used in calculations.
That column just indicates briths, marriages, deaths, etc..

Might be better in a database really, but I'm more used to Excel.

Will look up the links etc. this evening.

Cheers

 

[Guest]

Ah ha ! Took a while to work though the "Insert / Function / Date&Time"
stuff, think the diagram is for an earlier version of Excel.

Doesn't work automatically I see, has to be entered
=XDATE(year,month,date,"format"), but hey ! It works

A quick Global Replace should sort things out : sorted, thanks.

 

[Guest]

Ah and I have now discovered how to send a reply to one and not all

Live & learn.