Excel on Macbook

Excel for Mac uses several layers of settings for dates, so behavior can differ between what is typed, how the cell is formatted, and the workbook’s internal date system.

To make dates consistently appear as dd/mm/yyyy on Mac and on Excel for the web, use these checks and steps:

1. Confirm the cells are real dates, not text
   • Select a cell that shows the “wrong” format.
   • Change its format to General (Home → Number Format → General).
   • If it stays like 12/03/2024, it is text; if it becomes a number (e.g., 45350), it is a real date.
   • Text values will not reliably follow date formats; convert them to dates (for example, by re‑typing or using a formula) and then apply a date format.
2. Apply a consistent date format in Excel for Mac
   • Select the date cells.
   • Press Control+1 or Command+1 to open Format Cells.
   • Go to Number tab → Category: Date.
   • In Locale (location), choose English (India) or the desired locale.
   • Under Type, pick a format that matches dd/mm/yyyy (or closest), or:
     • Switch Category to Custom.
     • In Type, enter: dd/mm/yyyy.
   • Click OK.
3. Understand that some formats follow system region, some do not
   • In the Date category, formats that start with an asterisk (*) automatically follow the macOS regional date/time settings.
   • Formats without an asterisk do not change with system settings and may show differently than expected.
   • For strict consistency, use a Custom format dd/mm/yyyy instead of a starred format.
4. Check for column width issues
   • If a cell shows #####, the column is too narrow.
   • Double‑click the right border of the column header to auto‑fit, or drag it wider.
5. Use the same format in Excel for the web
   • In Excel for the web, select the date cells.
   • On the Home tab, open Number Format and choose Short Date or Long Date.
   • If the default still appears as m/d/yyyy, create/adjust the format in the desktop app using Custom dd/mm/yyyy, save the workbook, then reopen it in the web version so it uses that custom format.
6. Avoid mixing workbooks with different date systems (1900 vs 1904)
   • When opening files created on different platforms, Excel can switch between the 1900 and 1904 date systems, which affects the underlying serial numbers (and can shift dates by 4 years and 1 day).
   • In Excel (desktop), check File → Options → Advanced → When calculating this workbook → Use 1904 date system. Ensure all related workbooks use the same setting to avoid inconsistent date values.

After these steps, all cells that contain real dates and are formatted with a custom dd/mm/yyyy format should display consistently, regardless of whether Excel is on Mac or on the web.

References:

• Format a date the way you want in Excel
• Number format codes in Excel for Mac
• Change the date system, format, or two-digit year interpretation