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

G

Gazz

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

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
 
G

Gary''s Student

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.
 
G

Gazz

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
 
G

Gazz

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.
 
Top