Forum Discussion
Cannot enter dates in English (Australia) format
Hi,
I just spent about 1 hr with Microsoft 365 live chat and went around in circles without resolving the issue.
Here are the details:
- Using Mac Sonoma latest version with m3 max and excel (Microsoft 365)
- I have confirmed my Language and region settings are for Australia
I tried adding EXCEL to the customized Applications at the bottom but this didn't change the results.
When I enter the Australia date format it reverts to USA format.
When dates start from '13' ie 13th June 2024 --> 13/6/24 they will display correctly because this would not work in USA format as 6/13/24 is an invalid date.
Hopefully someone out there has a fix for this.
Thanks!
5 Replies
It seems like your Excel is defaulting to the USA date format even though your language and region settings are configured for Australia. Here are a few steps you can try to resolve this issue:
- Check Excel's Language Preferences: Ensure that Excel's language preferences are also set to English (Australia). You can usually find this setting under Excel Preferences > General > Language.
- Change System Date Format: Sometimes, Excel takes the date format from the system settings. Make sure your Mac's system date format is also set to English (Australia). You can adjust this in your Mac's System Preferences > Language & Region > Advanced > Dates.
- Check Date Format Cells: Ensure that the cells where you are entering dates are formatted correctly. You can do this by selecting the cells, right-clicking, choosing Format Cells, and then selecting the appropriate date format under the Number tab.
- Use Text-to-Columns Feature: As a workaround, you can use the Text-to-Columns feature to parse dates. Select the column with dates, go to Data > Text to Columns, choose Delimited, and then select the appropriate date format. This may help Excel recognize the dates correctly.
- Repair Office Installation: If none of the above steps work, try repairing your Office installation. Sometimes, corrupted installation files can cause unexpected behavior.
The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Hi,
thanks for your response!
I cannot find Excel's language preferences in the Mac version. Maybe you're thinking of the Windows version?
Steps 2 and 3 I have already done.
Step 4 is a possible workaround if I type all my dates using the general format. I may give this a go - extra time unnecessarily but if no other options will try it..
Anyone else in the forum have other ideas?Apologies for the confusion. You're correct that the language preferences in Excel are more prominent in the Windows version. In the Mac version, Excel typically takes its language and regional settings from the system preferences.
Since you've already checked and adjusted your Mac's system date format and formatted the cells correctly, and you're considering using the Text-to-Columns feature as a workaround, let's explore another potential solution:
You can try creating a custom date format that mimics the Australian date format explicitly. Here's how you can do it:
- Select the cells containing dates.
- Right-click and choose "Format Cells..."
- In the Format Cells dialog box, go to the "Number" tab.
- Select "Custom" from the list on the left.
- In the "Type" field, enter the custom date format: dd/mm/yyyy
- Click OK to apply the custom format.
This custom format explicitly tells Excel to display dates in the format Day/Month/Year, which should match the Australian date format.
