4-Digit Date Conversion - Excel Using the Wrong Century

[jessiright77]

I am importing client data from a UNIX database into an Excel
worksheet. Approximately 10% of the clients' birthdates are
converting over into the wrong century (ie., born 2028 instead of
1928).

When you view the client data in the database, however, the 4-digit
date of birth displays correctly as 1928.... so something is happening
in the conversion process.

Does anyone know what may be happening?

Thanks,
Jessi

 

[Gord Dibben]

Jessi

The century is determined by your Windows Regional and Language Settings.

Start>Settings>Control Panel>Regional and Language Options>Regional
Options>Customize>Date>Calendar.

Make changes here to let Windows how you want a date interpreted.

In your case change to 1928 or earlier.


Gord Dibben Excel MVP

 

[JessiRight77]

Thanks so much!

I'm not at work right now, but I'll test this tomorrow morning.

I really appreciate the tip,
Jessi

 

[JessiRight77]

Okay... Evidently, I still don't understand how this works.

I ran a test on my home computer:
1. I changed the values in Windows Regional and Language Date Settings
to interpret a 2-year digit as falling between 1900 and 1999.
2. I then created a text document in Notepad and entered the
following:
Smith, 01/18/28
Jones, 10/31/56
Abee, 12/3/13
3. I then imported this file as a comma-delimited file into Excel, and
formatted the second column as a MDY date field (during the import
process).


I expected Excel to convert the data as follows:
Smith, 01/18/1928
Jones, 10/31/1956
Abee, 12/3/1913

BUT... it imported it like this instead:
Smith, 01/18/2028
Jones, 10/31/1956
Abee, 12/3/2013

Why did it still convert the first and third entries as 21st-century
years (since both 28 and 13 fall between 1900 & 1999); and
Why did it treat 56 differently by converting it with 20th-century
formatting (1956)?

Sorry so slow!!
Jessi