Date Order - RESOLVED!

[VoG]

I live in the UK where the convention is to write dates as dd/mm/yy. However, Excel is determined to use the convention mm/dd/yy. I have verified this by

MsgBox(Application.International(XlDateOrder))

which returns 0, the code for mm/dd/yy.

The effect is that if I enter 29/3 (to mean 29 March) in a cell it will appear as "29/3" whatever date format I set. If I try e.g. =WORKDAY() on that cell I get #VALUE! If I enter 3/29 all is OK.

I have checked in Control Panel, Regional and Language settings and in Date and Time and all appears OK.

What have I missed or where am I going wrong? I am sure that this has not always occurred but as I don't use date functions that often I cannot pinpoint when things changed.

Excel 2002 SP2 and Windows XP SP1.
This message was edited by VoG™ on 2002-10-05 12:33

 

[Mudface]

Hmm, that gives a 1 for my version of Excel 2000. My Regional settings are Language- English (UK) and my short and long date format are both day-month-year, it might be worth rechecking the settings on your computer.

 

[VoG]

Mudface


Thanks but.... my settings are exactly the same as yours. That's why it is a mystery. Oh, and I've tried setting them again, Apply, rebooting and so on. No difference.

 

[VoG]

Sorted - on my nth visit to Control Panel, I changed the settings to "US", Apply, OK. Then back again to "UK", Apply, OK. It worked - why I don't know.

 

[ylijohe]

So what is there to do to avoid "climbing the tree **** ahead" as we finns say when something is done totally wrong?

The only logical way to format the date is d.m.yyyy.

To achieve deeper knowledge of time check this profound link:

http://www.mvps.org/dmcritchie/excel/datetime.htm
This message was edited by ylijohe on 2002-10-05 16:03