Importing .txt data into Excel

D

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?
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem sorting by date after 2029 on Excel 2003 1
On an import to excel a two digit date still is not right after c. 4
Importing a space-delimited txt. file right-justified into Excel 2
automate opening a .txt file 3
Import txt file into excel with 281,344 rows 2
importing from a *.txt file 4
Import Several .txt Files 2
Excel date formats 2

Top