Inconsistent behaviour interpreting dates, variously as dd/mm or mm/dd

[stefanaalten]

My locale setting is set to English (United Kingdom). Date and time notation in the UK is done using the day-month-year format. However, despite this setting, Excel seems to interpret dates variously as US (month-day) or UK (day-month) format. A few examples to illustrate my question - I open a new blank spreadsheet and enter the following:
Ex. 1) Enter "10/11/12". This should be interpreted as 10th November 2012 (dd/mm/yy), but is interpreted as 12th November 2010 (yy/mm/dd)
Ex. 2) Enter "10/11". Should be interpreted as 10th November (of current year, in this case 2024), but is interpreted as 11th October (of current year).
Ex. 3) Enter "13/11". Should be interpreted as 13th November (of current year), and IS (correctly) interpreted as 13th November.
Examples (2) & (3) show contradictory behaviour - in example (2) the entered date is interpreted as being in "mm/dd" format, whereas in example (3) the entered date is interpreted as "dd/mm" format.
How can specify that Excel should - by default - treat dates as being entered in dd/mm (or dd/mm/yy as the case may be) format, and NOT as mm/dd or - even worse - as variously either dd/mm or mm/dd!
Many thanks!

 

[felixstraube]

From my experience Excel interprets dates depending on the format the OS is set to (if you are on a desktop application).

You can change this in regional settings in the control panel:

In (1) you can first select a country preset,
Then in (2) you can select different date format presets.
And finally if you want to customize it you can do that going to additional configuration and date tab and modify the date format in (3)

Once this is set as you need it. Excel should recognize the dates entered as expected.

 

[stefanaalten]

Thanks for your response Felix. Mine is (and always has been) set to "English (United Kingdom)" and I have specified the date formats as shown (sort date yyyy-MM-dd, and long date d MMMM yyyy).

I think the penny is slowly dropping though ... If yyyy is omitted from a date entry, then it will presumably be taken as MM-dd ...

 

[MARK858]

I'm in the UK and I have my short date as dd-mm-yyyy, your Ex. 1 and Ex. 2 are probably caused by your yyyy-mm-dd