Importing .txt data into Excel

[Dave]

I am trying to import a .txt file with a 6 digit date into Excel. The date
is formatted as a mmddyy and when changing this field to a date excel seems
to translate any yy >= 30 as 1930 instead of 2030?

 

[Bernie Deitrick]

Dave,

That is the default break point for two digit year data entry. If you need 010135 to be Jan 1,
2035, then use an four digit year in the txt file creation.

Otherwise, use a formula to fix the dates - say that you will have no dates in the 1900s:

=IF(YEAR(A2)<2000,DATE(YEAR(A2)+100,MONTH(A2),DAY(A2)),A2)

then copy down and paste special values over the original dates.

HTH,
Bernie
MS Excel MVP