2-digit years in imported text files
Posted by Brenda Hernandez on January 24, 2002 7:49 AM
I have changed my regional options settings to interpet all years as falling between 2000 and 2099. I can enter a 2-digit date such as 6/1/36 directly into a cell and Excel will correctly interpet it as 6/1/2036. However, when I import a text file with a column of 2-digit dates, that same entry of 6/1/36 will be interpeted as 6/1/1936. I need to sort on date once the file is imported so I can't leave the date column as text.
Posted by Mark W. on January 24, 2002 10:22 AM
Without doing a great deal of research here's
my interpetation of what's happening...
The change you made to your Regional settings is
implemented at the operating system level. It
recognizes that you're typing a valid date and
translates it before Excel ever "sees" it. On
the other hand when you import a date using
the Text to Column... wizard the OS is never
involved. Excel's treatment of 2-digit years
(as described in the Help topic, "How Microsoft
Excel handles dates in year 2000 and beyond")
prevails.
Fundamentally, you have 2 choices...
1. Have the provider of the data convert to
a 4-digit representation of the year. This (in
my opinion) should be the preferred approach --
an important lesson from the recent Y2K crisis.
2. Convert the erroneous date after import using
the formula...
=DATE(20&RIGHT(YEAR(A1),2),MONTH(A1),DAY(A1))
