| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Wrap Text is not working correctly on one persons computer | Excel Discussion (Misc queries) | |||
| excel vlookup not working correctly | Excel Worksheet Functions | |||
| Convert Text to Columns with Excel 2007 beta does not work well | Excel Worksheet Functions | |||
| Excel 2007 Beta 2 Error -- Text-to-Columns Wizard Data preview wha | Excel Discussion (Misc queries) | |||
| Excel 2003 Subtotals not working correctly | Excel Worksheet Functions | |||
Linear Mode
