Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Date formats | Excel Discussion (Misc queries) | |||
Excel Date Formats | Excel Discussion (Misc queries) | |||
Excel Date Formats | Excel Discussion (Misc queries) | |||
Excel Date Formats | Excel Discussion (Misc queries) | |||
Excel Date Formats | Excel Discussion (Misc queries) |