date format not correct

[Guest]

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.

 

[Peo Sjoblom]

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

 

[Guest]

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.

 

[Peo Sjoblom]

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 file>open 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