Problem with sorting 19th century dates

[Robert Judge]

I have an EXCEL 2003 worksheet that includes a column of the birthdates of
about 500 people, including some with birthdates in the 1800's and 1700's.
The data has been derived from a GEDCOM file, the standard for exporting
genealogy programs into other programs such as EXCEL.

I have formatted the cells as Date "3/14/2001", hoping to sort the
birthdates chronologically. However, the the nineteenth century dates, for
example January 1, 1890, refuse to sort properly. It appears as if EXCEL
does not know how to handle a nineteenth century date.

Is there a trick to formatting cells which contain dates before 1900? I
will appreciate advice.

 

[Ron Rosenfeld]

I have an EXCEL 2003 worksheet that includes a column of the birthdates of
about 500 people, including some with birthdates in the 1800's and 1700's.
The data has been derived from a GEDCOM file, the standard for exporting
genealogy programs into other programs such as EXCEL.

I have formatted the cells as Date "3/14/2001", hoping to sort the
birthdates chronologically. However, the the nineteenth century dates, for
example January 1, 1890, refuse to sort properly. It appears as if EXCEL
does not know how to handle a nineteenth century date.

Is there a trick to formatting cells which contain dates before 1900? I
will appreciate advice.

From

HELP Excel Specifications and Limits:

Earliest date allowed for calculation January 1, 1900
(January 1, 1904, if 1904 date system is used)

Latest date allowed for calculation December 31, 9999

-----------------------------------------------------------

There are VB solutions available for handling dates prior to 1 Jan 1900, but
native Excel does not.

--ron

 

[Martin Fishlock]

As Ron correctly states there is little one can do for handling dates before
1900 as date values.

Have a look at John Walkenbach's solution on
http://j-walk.com/ss/excel/files/xdate.htm

Or you can split up the date and treatment as seperate day, month and year
as in
if the date is in A1:

month= =LEFT($A$1,FIND("/",$A$1,1)-1)
day= =MID($A$1,FIND("/",$A$1,1)+1,
FIND("/",$A$1,FIND("/",$A$1,
1)+1)-1-FIND("/",$A$1,1))
year= =RIGHT($A$1,LEN($A$1)-FIND("/",$A$1,4))

And treat them seperately.