How to Fix Excel Showing January for Every Month Number
Question details
Users attempt to format a standalone month number (e.g., 12) using the TEXT function or Custom Format with 'mmmm' to display the month name, but Excel always outputs 'January'.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting numbers into full month names using Custom Cell Formatting or the TEXT function.
- Observed behavior
- Excel interprets standalone numbers as date serial numbers (e.g., the number 12 becomes January 12, 1900) and incorrectly outputs 'January' for every number from 1 to 12.
Check your source data to determine whether your cells contain full calendar dates (e.g., 12/01/2023) or just standalone month numbers (e.g., 1 through 12).
Use a Complete Date Reference in the TEXT Function
The standard and most reliable method is to apply the TEXT function to a cell that contains a full, valid date rather than a standalone number.
Excel stores dates as sequential serial numbers starting from January 1, 1900. When you format the number 12 as a month, Excel sees it as the 12th day of 1900, which falls in January. Providing a full date prevents this misinterpretation.
Ensure your reference cell (e.g., D7) contains a complete date, such as 12/15/2023.
Select an empty cell where you want the month name to appear and type the formula =TEXT(D7, "mmmm").
Press Enter to execute the formula. The cell will now display the correct full month name, such as 'December'.

Convert a Standalone Number Using the DATE Function
If your dataset only has numbers 1 through 12 and you cannot use full dates, combine the TEXT and DATE functions to force Excel to recognize the correct month.
Apply Custom Cell Formatting Directly to a Date
Display the month name dynamically without using any formulas by simply changing the cell's custom number format.
Extract and Format Months Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful date and time functions that behave exactly like Microsoft Excel, allowing you to format dates and troubleshoot formula errors effortlessly. It features an intuitive interface to help you manage data without formatting headaches.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date or month number data.
- 2. Select a target cell: Click on a blank cell where you want the properly formatted month name to appear.
- 3. Enter the formula: Type =TEXT(DATE(2023, A1, 1), "mmmm") if A1 contains a standalone number, or =TEXT(A1, "mmmm") for a full date cell.
- 4. View results instantly: Press Enter to instantly see the correct month name, fully compatible with Excel's date serial system.

Frequently Asked Questions
Why does Excel show 1/0/1900 for blank date cells?
When referencing an empty cell, Excel interprets the blank value as a 0. In Excel's date serial system, the number 0 corresponds to the fictitious date of January 0, 1900. Formatting this empty reference as a month will result in 'January'.
Does the MONTH function return the name of the month?
No, the MONTH function only extracts the month number (1 through 12) from a given date. To convert a date into a text string containing the month's name, you must use the TEXT function.
How do I get the short month name, like 'Dec', in Excel?
To display the abbreviated three-letter month name, use 'mmm' instead of 'mmmm' in your TEXT function. For example, =TEXT(A1, "mmm") will output 'Dec' for a December date.




