Date Order - RESOLVED!

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,385
Messages
6,136,276
Members
450,001
Latest member
KWeekley08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top