Years before 1900

[danDanEFC]

Hi

I am helping my Dad with his family tree by making spreadsheets with the info he gets.

When I try and sort by date excel is ok with the dates from 1900 onwards but it the dates in the years before 1900 it ignores and orders them by the first number i.e when sorted they turn out like this

01/01/1902
01/02/1935
01/03/1935
01/01/2001
01/07/1896
02/06/1894
03/05/1802

We use office 2000 I think
any help would be great.

danDan

ps i have looked/searched for other topics

[jetted]

Hi

Excel supports two date systems. The first (and default) is based on 1900, and the second is based on 1904. If you use the 1904 date system you can use dates prior to 1900, since this date system supports negative dates. To changes this go to menu Tools-->Options--> check the tab Calculation and check the box 1904 date system, and your sort should now work.

Thanks
Denis

[oldchippy]

Hi

I am helping my Dad with his family tree by making spreadsheets with the info he gets.

When I try and sort by date excel is ok with the dates from 1900 onwards but it the dates in the years before 1900 it ignores and orders them by the first number i.e when sorted they turn out like this

01/01/1902
01/02/1935
01/03/1935
01/01/2001
01/07/1896
02/06/1894
03/05/1802

We use office 2000 I think
any help would be great.

danDan

ps i have looked/searched for other topics

Hi danDanEFC,

You might be interested in this link on dates before 1900 in Excel

http://support.microsoft.com/kb/245104

oldchippy

[Desert Piranha]

Hi

I am helping my Dad with his family tree by making spreadsheets with the info he gets.

When I try and sort by date excel is ok with the dates from 1900 onwards but it the dates in the years before 1900 it ignores and orders them by the first number i.e when sorted they turn out like this

01/01/1902
01/02/1935
01/03/1935
01/01/2001
01/07/1896
02/06/1894
03/05/1802

We use office 2000 I think
any help would be great.

danDan

ps i have looked/searched for other topics

Hi danDanEFC,

You may want to have a look at this add-in.

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