Text to Columns not working correctly with dates in Excel 2007
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 |
Text to Columns not working correctly with dates in Excel 2007
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" wrote: 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 |
Text to Columns not working correctly with dates in Excel 2007
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? "tim m" wrote: 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" wrote: 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 |
Text to Columns not working correctly with dates in Excel 2007
Why not set to MDY instead of DMY?
-- David Biddulph "AL123" wrote in message ... 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? "tim m" wrote: 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" wrote: 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 |
Text to Columns not working correctly with dates in Excel 2007
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" wrote: 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? "tim m" wrote: 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" wrote: 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 |
Text to Columns not working correctly with dates in Excel 2007
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). "David Biddulph" wrote: Why not set to MDY instead of DMY? -- David Biddulph "AL123" wrote in message ... 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? "tim m" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 01:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com