On An Import To Excel A Two Digit Date is the wrong century.

[scyllanbay]

On my companies host system we store dates as 1-Feb-30. When I create an extract of these dates and import into excel they are not interpreted as the correct century. The same date in my import will come out as 12/1/1930. I think it has something to do with the import but I am not sure what. Has anyone ran into this and know of a fix?

Thanks!

[Ron Coderre]

That's the problem with 2-digit-year dates. Excel needs to assign a 4-digit year to them based on specific logic. Currently, Excel 2007/2010 cuts off at 30. Years before that are assigned to this century (2000's). Years 30 and greater are assigned to last century (1900's).

There are various approaches to correcting that issue for you, depending on the type of file you're importing and its contents.

[djapigo]

Hi scyllanbay,

I don't have much of an answer for you... I think Excel thinks any dates imported with only 2 digit years can only be dates that has already happened... so since 2030 hasn't happened yet, it will think '30' as 1930.

You might have to pick a year and create a formula like =if(year(A1)<=1930,date(year(A1)+100,month(A1),day(A1),A1)

Sorry, can't be more help...