Solved: UK Date Format Issue on a new Apple Mac with Microsoft Excel:mac 2008

An annoying issue (for UK users) is of the date format defaulting to US (MM:DD:YY) rather than UK (DD:MM:YY). This occurs when transferring to a new Apple Mac computer with a previously correctly working installation of Microsoft Excel:mac 2008 (from an older computer, but running the same version of OS X). In theory, there should be no change, but hidden somewhere in the setup of the new computer lurks something preventing use of the UK date format.

The problem shows itself when, in a new Excel worksheet, a date is entered, e.g. using the formula =TODAY() returns something like 11/22/16 (for 22nd November 2016) instead of 22/11/16. Worse, if an existing workbook is opened, which had previously  correctly UK-formatted dates, they are all changed to the US-variation. A particular ongoing problem is then generated when new dates are added to the sheet, as entering 1/12/16 is then taken as 12th January not 1st December 2016.

 It turns out that the issue manifests itself in any User account set up on the Mac computer EXCEPT the ‘Guest’ account, which mysteriously sets the date to the UK-format. Hence this provides the clue to solving the problem. After some searching and trial-and-error, I have identified that the issue relates to the absence of a key file: com.apple.HIToolbox.plist, which for some reason is not created when setting up a new User account (and may not be copied across if importing from another computer). Luckily this file is created when opening the Guest account, which can then be copied across to all required User accounts.

Here is a detailed summary of the procedure I eventually used to fix this date format issue:

1. Create and open a Guest Account (if not already enabled, go to Users & Group Preferences)
2. Go to the Library folder (it’s a hidden folder, so to find it, use Finder to go to the home folder, then select from the menu ‘Go:Go to Folder’ and type in ‘Library’)
3. Go to the Preferences folder
4. Make a local copy of the file: com.apple.HIToolbox.plist (e.g. onto an SD card, or external/cloud drive)
5. Now open your desired User account
6. Go to your Library folder (again it will be hidden, so find it as above)
7. Go to the Preferences folder
8. Copy the file: com.apple.HIToolbox.plist (from local storage) into this folder
9. Quit Excel and then log out of your user account, and then log back in
10. Next time Excel starts, UK date format will be correct!