Excel changes dates to 2004 automatically.

[ckmoied]

after importing data from text file, excel converts all the dates after 2000 to 2004.

For instance, the first column in all the text files is as follows:

Nov-00
Dec-00
jan-01
.
.
.
Dec-03

But once this text file is opened or imported into excel, then all dates after Dec-00 are automatically converted to MMM-04. as follows.

Nov-00
Dec-00
Jan-04
.
.
.
Dec-04

Any clues to resolve this problem, and why does it happen.

 

[Chitosunday]

This is because excel is confused when you only input the month and the last two digits of the year. If it sees only jan-00, it understand that it is month and year as there is no 00 in day and excel will put 1 as the default day automatically but if you input Jan-01, it reads it as month-day format and excel automatically put the default year which is the current year . To avoid this, your data should the full year like jan-2001 or import it as text (step 3-3) of import wizard data type.