Text to Columns not working correctly with dates in Excel 2007

[AL123]

I'm using Excel 2007 and when I convert a CSV file using "Text to Column"
menu command when the day is greater than 12 it does not convert the date
correctly. As can be seen in my example below hours 10:00 PM and 11:00 PM are
handled correctly for January 12 but hours 00:00, 01:00, and 02:00 for
January 13 are not. The "Text to Column" menu command works OK on this CSV
file in Excel 2003.

Any help is appreciated.

Format:
dd/mmm/yyyy h:mm AM/PM

CSV Text to Column
------------------------------- ---------------------------------------------

Date_Time,Value Date_Time Value
01/12/2003 22:00:00,689.2, 01/Dec/2003 10:00 PM 689.2
01/12/2003 23:00:00,626.4, 01/Dec/2003 11:00 PM 626.4
01/13/2003 00:00:00,558.8, 01/13/2003 00:00:00 558.8
01/13/2003 01:00:00,530.8, 01/13/2003 01:00:00 530.8
01/13/2003 02:00:00,512.8, 01/13/2003 02:00:00 512.8

 

[tim m]

Your format says day month year thus a number above 12 will screw things up.
I think you have your days and months mixed up format wise.

 

[AL123]

No I don't think so for the following reasons.
- In Step 3 of 3 in the "Convert Text to Column" wizard in the customer data
format box I have the Date selector field set to "DMY", and
- It works OK in Excel 2003

Any other thoughts?

 

[David Biddulph]

Why not set to MDY instead of DMY?

 

[AL123]

I found the problem. The PC which Excel 2003 work correctly on had it's
regional setting set to "English (United States)" while the PC which Excel
2007 didn't work correctly had it's regional setting set to "English
(Canada)". Once I set the regional setting to "English (United States)" it
worked OK. Thanks tim for your response as it pointed me in the right
direction.

 

[AL123]

I just tried that and it didn't work. It seems I need to set my regional
setting to "English (United States)" and then it works OK. Changing the Date
selector field in Step 3 of the wizard to "DMY" or "MDY" doesn't seem to make
any difference (I wonder what it's good for).