Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format not correct
Hi. I've created a spreadsheet Book1.xls, set the dateformat, set a few
cells, but the date displayed is not correct if the day number <= 12. My Windows locale is "English (United States)", short date is "6/15/2007", and location is "United States". Created a new xls file Book1.xls. Select column A and set the format to Date - English (United Kingdom) - 14/03/2001. In cell A2 enter "11/06/2007". The cell contents bar at the top shows the value is "11/6/2007" (which is fine), but the value displayed value in A2 "06/11/2007" which is not correct. Interestingly, if you press F2, then the in place cell editor shows "11/6/2007" (which is the value in the contents bar at the top). In cell A3 enter "12/6/2007". The cell contents bar at the top shows "12/6/2007", but the value displayed in A3 is "06/12/2007" which is not correct. In cell A4 enter "13/06/2007". The cell contents bar at the top shows "13/6/2007", and the value displayed in A4 is also "13/6/2007" which is good. Why is it that if the day number 12 then the format works fine? But if day number <= 12 the format reverts to US format. Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format not correct
No need to do that, if you are sending a spreadsheet to somebody with UK
date format it will convert automatically, if you really want to change it to UK format you need to change the regional setting under windows control panel otherwise for display you can use a custom format like dd/mm/yy but as long as you are using US format you need to enter it as mm/dd/yy and it will display correctly as dd/mm/yy but you cannot enter it as dd/mm/yy without changing windows date format -- Regards, Peo Sjoblom "Siemel Naran" wrote in message ... Hi. I've created a spreadsheet Book1.xls, set the dateformat, set a few cells, but the date displayed is not correct if the day number <= 12. My Windows locale is "English (United States)", short date is "6/15/2007", and location is "United States". Created a new xls file Book1.xls. Select column A and set the format to Date - English (United Kingdom) - 14/03/2001. In cell A2 enter "11/06/2007". The cell contents bar at the top shows the value is "11/6/2007" (which is fine), but the value displayed value in A2 "06/11/2007" which is not correct. Interestingly, if you press F2, then the in place cell editor shows "11/6/2007" (which is the value in the contents bar at the top). In cell A3 enter "12/6/2007". The cell contents bar at the top shows "12/6/2007", but the value displayed in A3 is "06/12/2007" which is not correct. In cell A4 enter "13/06/2007". The cell contents bar at the top shows "13/6/2007", and the value displayed in A4 is also "13/6/2007" which is good. Why is it that if the day number 12 then the format works fine? But if day number <= 12 the format reverts to US format. Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format not correct
"Peo Sjoblom" wrote:
No need to do that, if you are sending a spreadsheet to somebody with UK date format it will convert automatically, if you really want to change it to UK format you need to change the regional setting under windows control panel otherwise for display you can use a custom format like dd/mm/yy but as long as you are using US format you need to enter it as mm/dd/yy and it will display correctly as dd/mm/yy but you cannot enter it as dd/mm/yy without changing windows date format Thanks. If I enter the date as "6/11/2007" it displays correctly as "11/06/2007". But if I have a text file that has dates in dd/mm/yyyy format and paste it into my Excel spreadsheet, the result will not be correct. Is there a smart paste that will convert dates in dd/mm/yyyy format to mm/dd/yyyy format? Or do I have to modify the text file (by scripts, macros, etc) to physically convert the text so that paste into Excel will work? Thanks again. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format not correct
"Siemel Naran" wrote in message ... "Peo Sjoblom" wrote: No need to do that, if you are sending a spreadsheet to somebody with UK date format it will convert automatically, if you really want to change it to UK format you need to change the regional setting under windows control panel otherwise for display you can use a custom format like dd/mm/yy but as long as you are using US format you need to enter it as mm/dd/yy and it will display correctly as dd/mm/yy but you cannot enter it as dd/mm/yy without changing windows date format Thanks. If I enter the date as "6/11/2007" it displays correctly as "11/06/2007". But if I have a text file that has dates in dd/mm/yyyy format and paste it into my Excel spreadsheet, the result will not be correct. Is there a smart paste that will convert dates in dd/mm/yyyy format to mm/dd/yyyy format? Or do I have to modify the text file (by scripts, macros, etc) to physically convert the text so that paste into Excel will work? Thanks again. If you have a text file then open it via fileopen from within excel, that way it will start the text import wizard, then click next twice and in step 3 under column data format select Date and DMY (always use the same format as the original so if they are UK dates use DMY), then click finish then it will convert correctly into US format You can of course automate this using VBA -- Regards, Peo Sjoblom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I correct a custom format? | Excel Discussion (Misc queries) | |||
Correct Format for formula | Excel Worksheet Functions | |||
Date Format Correct, But Formula Error | Excel Discussion (Misc queries) | |||
Date format not correct when you convert a CSV text file in Excel | Excel Discussion (Misc queries) | |||
How do i format a 5 to 6 digit number into the correct date? | Excel Worksheet Functions |