How to Fix Excel Dates Showing an Invalid Month (e.g., 31/0/2023)
Question details
Users encounter a formatting error where dates display with a zero instead of a valid month number, such as '31/0/2023'.
- Product
- Microsoft Excel
- Device & OS
- Windows and macOS
- Scenario
- Applying custom number formats, importing CSV data, or using Flash Fill to populate date columns.
- Observed behavior
- Excel displays a '0' instead of the correct month number due to misinterpreting custom format codes (m vs M) or conflicting regional system settings.
Check if the problematic dates align to the right (indicating they are valid numbers with a display error) or to the left (indicating they are stored as raw text) to choose the appropriate troubleshooting step.
Correct the Custom Date Format in Excel
Reapplying a standard date format or fixing your custom format codes resolves the ambiguity between minutes and months.
In Excel's custom number formatting, the letter 'm' can represent both months and minutes. Depending on its placement, Excel might mistakenly infer that you want to display minutes, which can result in a '0' output for a date.
Highlight the range of cells in your worksheet that are displaying the invalid 31/0/2023 format.
Right-click the highlighted selection and choose 'Format Cells', or press 'Ctrl+1' (Windows) / 'Command+1' (Mac) on your keyboard.
Navigate to the 'Number' tab, select 'Date' from the Category list, and choose a valid locale-specific format (such as MM/DD/YYYY).
If you require a specific layout, select 'Custom' and use 'mm' or 'mm/dd/yyyy'. This explicitly forces Excel to display the month correctly instead of a single 'm', avoiding the minute interpretation.
Adjust OS Regional Date Settings
If standard date formats are consistently failing during CSV imports, aligning your operating system's regional settings with the data's origin will prevent misinterpretations.
Convert Text to Dates Using Power Query or Formulas
When dates are imported as static text or distorted by Flash Fill, converting them with Power Query or the DATEVALUE function restores their numerical integrity.
Fix Date Formatting Issues Easily with WPS Office
WPS Spreadsheet provides a highly compatible and intuitive interface to correct custom date formatting errors, ensuring your imported CSV data and regional date displays are perfectly accurate without frustrating '0' month errors.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook or CSV file containing the formatting errors.
- 2. Select the error cells: Highlight the date cells that are showing the invalid 31/0/2023 format.
- 3. Access the formatting tools: Right-click the selected area and choose 'Format Cells' from the context menu.
- 4. Apply a valid date format: Navigate to the 'Number' tab, click on 'Date', select your desired regional format, and click 'OK' to instantly resolve the issue.
Frequently Asked Questions
Why is my custom date format showing months as minutes?
In custom number formatting on English systems, Excel uses the lowercase letter 'm' for both months and minutes. Depending on the sequence of the format code (especially if placed directly after an hour code), Excel might infer 'm' as minutes. Switching your custom format to 'mm' explicitly forces it to display the month.
How do I fix incorrect dates when importing a CSV file?
Avoid double-clicking a CSV file to open it. Instead, open a blank Excel workbook, navigate to the Data tab, and select 'From Text/CSV'. This opens the import wizard where you can explicitly set the date column's locale and format (like MDY or DMY) before the data is loaded onto the sheet.
Can macOS regional settings cause Excel date errors?
Yes, if your Mac's system-wide date formats clash with the structure of the data you are importing, Excel will misinterpret the values. Go to macOS System Settings > General > Language & Region, adjust the Date format to match your workflow, and restart Excel to apply the changes.
