Excel date formats
 

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

Excel date formats
 

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


On Oct 22, 6:16*pm, mmclemore
wrote:
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

Excel date formats
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"mmclemore" wrote in message
...
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