Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting of date
Hello,
I have on a spreadsheet a column for dates. The dates are entered in the format dd/mmm/yyyy and then the cell formatting changes them to mmm-yy. This works fine for all UK users, but it all goes pear shaped when people abroad add dates. In this case the format of the cell is overridden with another format and foreign spelling. For example, if I entered 12/oct/2008 in the UK, the cell would display Oct-08. However when a Dutch person does it for the same date 12-Okt-2008 is displayed. Not only is the formatting wrong, but the word Okt instead of Oct is present. I have a macro which used the "Oct" part to count the number of entries per month and this issue is messing it up. Is there any way to force the dates into UK format? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting of date
The first thing to remember is that the formatting of a cell does not affect
its contents. The contents stay the same regardless of the formatting. Your basic problem is that the Dutch entry is not being recognized as a date. It is therefore being treated as text. It's got nothing to do with the format. I suspect the easiest thing you can do is to use month numbers rather than month names. For example, enter 12/10/2008 rather than 12/Oct/2008. And in your macro, look for month 10 rather than month Oct. But the bottom line is, make sure your entries are being recognized as a date, not as text. The easiest way to determine the difference is that dates can be reformatted, text cannot. Regards, Fred. "Libby" wrote in message ... Hello, I have on a spreadsheet a column for dates. The dates are entered in the format dd/mmm/yyyy and then the cell formatting changes them to mmm-yy. This works fine for all UK users, but it all goes pear shaped when people abroad add dates. In this case the format of the cell is overridden with another format and foreign spelling. For example, if I entered 12/oct/2008 in the UK, the cell would display Oct-08. However when a Dutch person does it for the same date 12-Okt-2008 is displayed. Not only is the formatting wrong, but the word Okt instead of Oct is present. I have a macro which used the "Oct" part to count the number of entries per month and this issue is messing it up. Is there any way to force the dates into UK format? Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting of date
I have only the US vision of Excel so I cannot check this but I know you can
use a county code in a custom format See http://office.microsoft.com/en-us/ex...346351033.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Libby" wrote in message ... Hello, I have on a spreadsheet a column for dates. The dates are entered in the format dd/mmm/yyyy and then the cell formatting changes them to mmm-yy. This works fine for all UK users, but it all goes pear shaped when people abroad add dates. In this case the format of the cell is overridden with another format and foreign spelling. For example, if I entered 12/oct/2008 in the UK, the cell would display Oct-08. However when a Dutch person does it for the same date 12-Okt-2008 is displayed. Not only is the formatting wrong, but the word Okt instead of Oct is present. I have a macro which used the "Oct" part to count the number of entries per month and this issue is messing it up. Is there any way to force the dates into UK format? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting of date
I have experimented.
In A1, a date 1/10/2008 was entered. I am Canadian so this is 1-Oct-2008 If I format is as dd-mmm-yy I see 1-Oct-2008 From the URL I sent you, I learnt that Germany's code is 407 If I format A1 with [$-407] dd-mmm-yy, then I se 1-Okt-08 The English(UK) code is 809 so if I use [$-809] dd-mmm-yy I see 1-Oct-08 My bet is that if I sent this to a Norwegian user she would see 1-Oct-08 Please try it and let us know the result -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Libby" wrote in message ... Hello, I have on a spreadsheet a column for dates. The dates are entered in the format dd/mmm/yyyy and then the cell formatting changes them to mmm-yy. This works fine for all UK users, but it all goes pear shaped when people abroad add dates. In this case the format of the cell is overridden with another format and foreign spelling. For example, if I entered 12/oct/2008 in the UK, the cell would display Oct-08. However when a Dutch person does it for the same date 12-Okt-2008 is displayed. Not only is the formatting wrong, but the word Okt instead of Oct is present. I have a macro which used the "Oct" part to count the number of entries per month and this issue is messing it up. Is there any way to force the dates into UK format? Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting of date
Hi Bernard and Fred,
Thank you both for the replies. Things are complicated slightly because the dates are added via a userform and a macro. The user enters the date on the form in the format dd/mm/yy and the form then changes this to dd/mmm/yyyy. I had to do this because without it there were instances where 01/06/08 would be interpreted as the 6th of January instead of the 1st of June. Neverthelesss, I shall certainly have a go at it and post the result. "Bernard Liengme" wrote: I have experimented. In A1, a date 1/10/2008 was entered. I am Canadian so this is 1-Oct-2008 If I format is as dd-mmm-yy I see 1-Oct-2008 From the URL I sent you, I learnt that Germany's code is 407 If I format A1 with [$-407] dd-mmm-yy, then I se 1-Okt-08 The English(UK) code is 809 so if I use [$-809] dd-mmm-yy I see 1-Oct-08 My bet is that if I sent this to a Norwegian user she would see 1-Oct-08 Please try it and let us know the result -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Libby" wrote in message ... Hello, I have on a spreadsheet a column for dates. The dates are entered in the format dd/mmm/yyyy and then the cell formatting changes them to mmm-yy. This works fine for all UK users, but it all goes pear shaped when people abroad add dates. In this case the format of the cell is overridden with another format and foreign spelling. For example, if I entered 12/oct/2008 in the UK, the cell would display Oct-08. However when a Dutch person does it for the same date 12-Okt-2008 is displayed. Not only is the formatting wrong, but the word Okt instead of Oct is present. I have a macro which used the "Oct" part to count the number of entries per month and this issue is messing it up. Is there any way to force the dates into UK format? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
Formatting Date | Excel Discussion (Misc queries) | |||
date formatting | Excel Discussion (Misc queries) | |||
XML Date formatting | Excel Discussion (Misc queries) | |||
formatting date | Excel Worksheet Functions |