Excel date formats

[mmclemore]

I have a problem importing dates with 2 digit year format. When importing
dates with a 2 digit year format, the conversion doesn't follow the regional
date and language settings. For example if I import 3/23/33, it is
interpreted as 3/23/1933 even though I've changed the regional date settings
to interpret up to 35 as the 21st century. If I type 3/23/33 directly into
Excel, it is interpreted as 3/23/2033 as expected. Why is the import process
not interpreting the correct century according to the regional date settings.

Thanks,

Mark

 

[muddan madhu]

go to regional setting | regional options | customize | date tab |
change the calender to 1950 AND 2049 | apply | ok | ok

 

[Bernard Liengme]

Not that it helps you very much, but I can confirm the same behaviour.
Even it I gave the file a TXT extension and used Text to Columns, the
28/2/33 became 28/3/1933
(sorry, I use the non-US convention)

Anyway you can modify the app that build the file?

Otherwise you will need something like
=IF(YEAR(A1)<2000,DATE(YEAR(A1)+100,MONTH(A1),DAY(A1)),A1)
This could go into a new B column , converted using Paste Special ->Values
and then the original A column deleted.
best wishes