Formula Errors

How to Fix Excel Showing January for Every Month Number

John WilsonJohn Wilson Oct 22, 2026 868 views

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'.

How to Fix Excel Showing January for Every Month Number
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.
Before you start

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).

Solution 1Recommended

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.

1
Locate a valid date

Ensure your reference cell (e.g., D7) contains a complete date, such as 12/15/2023.

2
Enter the TEXT formula

Select an empty cell where you want the month name to appear and type the formula =TEXT(D7, "mmmm").

3
Apply the formula

Press Enter to execute the formula. The cell will now display the correct full month name, such as 'December'.

Use a Complete Date Reference in the TEXT Function
Using Shortened Month Names: If you want the abbreviated month name (e.g., 'Dec' instead of 'December'), change the format code in the formula to "mmm".
Powerful Spreadsheet Tool

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date or month number data.
  2. 2. Select a target cell: Click on a blank cell where you want the properly formatted month name to appear.
  3. 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. 4. View results instantly: Press Enter to instantly see the correct month name, fully compatible with Excel's date serial system.
100% compatible with Microsoft Excel formulas, date formats, and custom formatting.Built-in function wizard to troubleshoot TEXT and DATE formulas with live previews.Lightweight, completely free, and runs smoothly on Windows, Mac, and Linux systems.
microsoft office alternative - wps office

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.