Formula Errors

Why Excel TEXT Returns January When Extracting a Month

Nimra MalikNimra Malik Nov 13, 2026 870 views

Question details

The user wants to display the abbreviated month from a date in cell E2.

Why Excel TEXT Returns January When Extracting a Month
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses =TEXT(MONTH(E2),"mmm") to extract a month name.
Observed behavior
The formula returns January instead of the expected month.
Before you start

Select E2 and check the formula bar to see whether the cell contains a complete date or only a month number. The appropriate formula depends on what E2 stores.

Solution 1Recommended

Apply TEXT Directly to the Date in E2

Use this method when E2 contains a valid Excel date.

MONTH(E2) returns a number from 1 to 12. When TEXT formats that number as a date, it treats the number as a date serial, producing a January month name rather than the month from E2.

1
Check the source date

Select E2 and confirm that it contains a complete date. If necessary, enter a valid date such as June 15, 2024.

2
Replace the formula

Select the result cell and enter =TEXT(E2,"mmm"), then press Enter. A date in June returns Jun.

3
Show the full month name

If you prefer the full name, replace the formula with =TEXT(E2,"mmmm") and press Enter.

Apply TEXT Directly to the Date in E2
A number alone is not a complete date: If E2 contains only 6, Excel treats it as a date serial when applying a date format. In the default 1900 date system, serial 6 represents January 6, 1900.
Work with dates in WPS Spreadsheets

Extract Month Names in WPS Spreadsheets

Use the TEXT and DATE functions in WPS Spreadsheets to display a month name from a date or a month number.

  1. 1. Open the worksheet: Open WPS Spreadsheets and select Open to load your workbook.
  2. 2. Check E2: Select cell E2 and check whether it contains a complete date or a month number.
  3. 3. Enter the month formula: Select the result cell and enter =TEXT(E2,"mmm") for a complete date. For a month number from 1 to 12, enter =TEXT(DATE(2000,E2,1),"mmm").
  4. 4. Confirm the result: Press Enter and check that the displayed month matches the value in E2.
Extract abbreviated or full month names with familiar formulas.Open and edit Microsoft Excel XLS and XLSX files.Use a familiar spreadsheet interface for everyday calculations.
microsoft office alternative - wps office

Frequently Asked Questions

Why does =TEXT(MONTH(E2),"mmm") return January?

MONTH(E2) returns a number from 1 to 12, not the original date. TEXT then interprets that number as a date serial and formats it as a month in January.

How do I display the full month name from a date?

If E2 contains a valid date, enter =TEXT(E2,"mmmm") in the result cell. Use "mmm" instead for an abbreviated name.

How can I turn the number 6 into Jun?

If E2 contains 6 as a month number, enter =TEXT(DATE(2000,E2,1),"mmm") in another cell. Use a month number from 1 through 12.

What if E2 looks like a date but the formula returns an error?

E2 may contain text rather than a recognized date. Select E2 and check the formula bar, then re-enter the value as a date recognized by your spreadsheet's regional settings.