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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

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

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

Board Regular
Joined
Sep 6, 2002
Messages
58
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
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

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
Top