Thread: Dates before 1900

I'm running Excel 2007 under Windows 7 X64.

I am trying to create a timeline for a family and the dates start in 1860 and extend to 1953. I've set a column to format as custom "d mmm yyyy" which gives things like 23 Dec 1920 or 7 Jun 1942. However, when I enter dates before 1900 things fall apart. If I try to enter 21 Jul 1872 it doesn't justify properly and treats it as text. If I try =date(1872,07,21) I get 21 Jul 3373.

Is there any way to produce a list of dates spanning 1900 such that I can sort them into chronological order?

Any advice would be appreciated.

Thanks

Tony

Hello Tony,

Natively excel doesn't cater for dates before 1900

John Walkenbach has an "add-in" which can help you use such dates, see here...although I'm not sure it'll let you sort them.

To do that you might need a helper column, e.g. put the age or something similar in one column (calculated with XDATE functions) and then sort everything by that new column

I didn't watch around but is there some year in future that will act exactly the same from the year you looking for, so you can use it instead?