'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Stop Excel from Changing Custom Date Formats on Mac
Question details
The user needs to prevent Excel from automatically reverting custom date formats to default regional settings upon selecting the Long Date preset.
- Product
- Microsoft Excel
- Device & OS
- macOS Sonoma (M1 MacBook Pro)
- Scenario
- Applying and maintaining a specific custom date format (yyyy-mm-dd) in an Excel spreadsheet.
- Observed behavior
- Excel automatically changes the custom applied date format back to the system's regional default layout when the Long Date option is selected.
Ensure your macOS and Microsoft Office installations are updated to the latest versions, as early macOS Sonoma releases contained known compatibility bugs affecting regional date formats.
Apply Custom Date Formats Without Using the Ribbon Presets
Avoid clicking 'Long Date' or 'Short Date' after applying a custom format, as these presets are designed to immediately override your custom settings.
In Microsoft Excel, selecting a standard format preset from the top ribbon will force the cell to adopt your Mac's default regional settings, erasing any custom formatting you previously applied.
Highlight the cells containing the dates you want to apply the custom format to.
Right-click the selected cells and choose 'Format Cells' from the context menu, or press the 'Command + 1' keyboard shortcut.
Go to the 'Number' tab, select 'Custom' from the category list, enter 'yyyy-mm-dd' (or your preferred format) in the Type box, and click 'OK'.
Do not click 'Long Date' or 'Short Date' in the Excel ribbon afterward. Leave the cells as they are to retain the custom formatting.
Sync macOS Regional Settings with Excel
Adjust your Mac's system-wide date settings so that Excel's default formats match your preferred layout.
Test Formatting in a New Workbook
Determine if the formatting issue is caused by a corrupted spreadsheet file rather than application settings.
Easily Format Dates with WPS Spreadsheet
Avoid frustrating formatting glitches on macOS by using WPS Office. WPS Spreadsheet provides a stable, highly compatible interface to apply and maintain custom date formats like yyyy-mm-dd without unexpected changes.
- 1. Open your file: Launch WPS Office on your Mac and open your existing spreadsheet document.
- 2. Select the cells: Highlight the specific cells containing the dates you wish to format.
- 3. Apply custom format: Right-click and select 'Format Cells'. Under the Number tab, choose 'Custom', type 'yyyy-mm-dd', and click 'OK'.
Frequently Asked Questions
Why does Excel change my date format to a number?
Excel stores dates as sequential serial numbers for calculations (e.g., January 1, 1900, is serial number 1). If a cell's format is accidentally changed to 'General' or 'Number', the date will display as its underlying serial number. You can fix this by highlighting the cell, opening Format Cells, and changing the category back to 'Date'.
How do I make yyyy-mm-dd the default date format in Excel for Mac?
Excel relies on your macOS system settings for its default date layouts. To change the default, go to Apple Menu > System Settings > General > Language & Region, and modify your system's short date format to yyyy-mm-dd. Excel will inherit this setting upon restart.
Can I use the same custom date formats in WPS Spreadsheet as I do in Excel?
Yes, WPS Spreadsheet fully supports Excel's custom number and date formatting syntax. Custom formats like 'yyyy-mm-dd' or 'dd-mmm-yy' will work flawlessly and display identically in both programs.
