Handling old Dates in Excel

[HDRider]

I am handling dates (Birth and Death) in Excel and when I import old
dates they become all messed up. As an example:

September 17, 1911 becomes September 17, 2011.

Also dates before 1900 do not convert and come in as text. This causes
a problem when sorting my file by the date.

To fix the first problem I must go in and manually change the year then
the cell is okay. But, changing every cell date becomes a problem and
is very time consuming.

Any suggestions?

Steve

 

[Dave Peterson]

First the easy one:

John Walkenbach has an addin that you can use for dates pre-1900. It's at:
http://j-walk.com/ss/excel/files/xdate.htm

I'm betting that your dates are 2 digit years. And excel guesses wrong for you
that 09/17/11 is 2011 (but would be ok for most of the rest of us!).

This is taken from the Excel's Help for "About dates and date systems"

How Excel interprets two-digit years

To ensure that year values are interpreted as you intended, type year values as
four digits (2001, rather than 01). By entering four digits for the years, Excel
won't interpret the century for you.

For Microsoft Windows 98 or Microsoft Windows 2000

If you are using Microsoft Windows 98 or Microsoft Windows 2000, the Regional
Options in Windows Control Panel controls how Excel interprets two-digit years.

For Windows NT Workstation 4.0 or dates entered as text values

When you enter a two-digit year value in Windows NT Workstation 4.0 or you enter
a date as a text value, Excel interprets the year as follows:

00 through 29 Excel interprets the two-digit year values 00 through 29 as the
years 2000 through 2029. For example, if you type the date 5/28/19, Excel
assumes the date is May 28, 2019.
30 through 99 Excel interprets the two-digit year values 30 through 99 as the
years 1930 through 1999. For example, if you type the date 5/28/98, Excel
assumes the date is May 28, 1998.

==============

I'm not sure how other versions of excel treat those dates (and what version of
windows and what version of excel you're using). You may want to check your
help system.

If that doesn't work, maybe you could use a column of helper cells and convert
the dates to what you want...

=IF(YEAR(A1)<2000,A1,DATE(YEAR(A1)-100,MONTH(A1),DAY(A1)))

With your date in A1.