How do you convert dates before 1900?

Question

I have an EXCEL 2010 file containing names and dates of birth and death.  I have formatted the date columns to he same date format.  However, when I sort by a date column, EXCEL will properly sort dates between 1900 and the present.  However, it will not properly sort dates before January 1, 1900.  For example, it will correctly sort February 5, 1900 by the year 1900), but it sorts February 5, 1899 by the day, not the year.  In other words, instead of:

05 Feb 1898

01 Mar 1898

03 Apr 1899

Excel sorts the above as:

01 Mar 1898

03 Apr 1899

05 Feb 1898

Sorting by the day, instead of the month

How can I format the date cells and then make EXCEL sort the dates by the year, not the day? 

I will appreciate advice.

Answer 1

Excel does not recognize dates earlier than Jan 1, 1900 as dates.  So Excel is looking at those date entries as plain text, and sorting accordingly.

To get them to sort by year, month and day you'd need to enter them as kind of pseudo dates with the parts of the date in that sequence:

yyyy mm dd

as

1898 03 01  for 01 March 1898  or even 1898/03/01

1899 04 03  for 03 April 1899 or 1899/04/03

1898 02 05  for 05 Feb 1898 or 1898/02/05

Then they should sort in order of years, months within the year, days within same month be sure to enter months and days with 2 digits, with the leading zero for months or days 1 through 9.

Here is the specifications and limits page for Excel 2010:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx

Answer 2

Firstly will XDATE work on Excel 2010?

I’m running Win 7 64bit

I have downloaded XDATE and can see it in File>Options>Add-Ins>Manage Excel Add-ins GO>Extended Date Functions (ticked the box & clicked OK in the Dialogue Box)> …………………..BUT can’t see Extended Date Functions in the Add-ins TAB.

Am I doing something wrong?

Answer 3

check this excellent 3rd party add-in

Extended date functions

hth,

hector.