Excel 2-digit years in imported text files

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.

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))

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

2-digit years in imported text files

Re: 2-digit years in imported text files