How do I change the default date format on Excel for Mac to DD/MM/YYYY

Question

I've been incredibly frustrated by this. I live in Europe and my standard date format is DD/MM/YYYY. So when I type in 01/05/2018 I expect Excel to show that or 1 May 2018. But it inverts it to MM/DD/YYYY. 

I've looked at many forum threads with similar questions but all I find is temporary and not full solutions. I've tried changing the format to DD/MM/YYYY but it only changes how the date is displayed so there is a discrepancy between how I type it and how it is shown.

I have all my computer settings to DD/MM/YYYY but Excel doesn't seem to be inheriting these.

Please help! What to do?

Answer 1

The default formats used by Excel for Dates, Times & Numbers is determined by the macOS System Preferences for Language & Region. Your message suggests that you may have made those changes, but was Excel running when you did? If so you must restart the program in order for the changes to go into effect.

While Excel is not running go to > System Preferences. Specific verbiage may differ based on your version of macOS but from there it should be simple to recognize the equivalent Language & Region pane in order to get to the Advanced options. The preferred format must be made for each of the four Date styles: Medium, Long, Full as well as Short.

Note: Just to clarify, even doing this correctly will not cause the dates in existing workbooks to display differently than when they were entered.

Answer 2

I am so happy and annoyed right now! Thanks for the help, Bob. Have tried that several times. It hasn't worked – but inspired by your answer I tried changing the region and that solved my problem. When the region is stated as 'Faroe Islands' (which is where I am) it doesn't recognise the DD/MM/YYYY date format when I type it in Excel. But then I changed the region to UK while still having faroese language set up – and it worked.