Formula Errors

How to Fix Excel Formula Errors When Converting Dates to Month Text

Maira Mehtab Oct 1, 2026 869 views

Question details

The user needs to correctly format a date as month text without the formula incorrectly returning 'January' or 'January 1900'.

How to Fix Excel Formula Errors When Converting Dates to Month Text

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Converting a standard date value into a text string representing the month (or month and year).
Observed behavior: Using the TEXT function combined with the MONTH function (e.g., =TEXT(MONTH(B3), "mmm")) incorrectly returns 'January' or 'January 1900' because the spreadsheet reads the extracted month number (1-12) as a date serial value.

Before you start

Ensure the cell you are referencing contains a valid date format and is not just a text string that visually resembles a date.

Solution 1Recommended

Use the TEXT Function Directly on the Date Cell

The correct way to extract the month name as text is to apply the TEXT function directly to the original date cell, bypassing the MONTH function entirely.

When you use the MONTH function, it extracts the month as a numerical value from 1 to 12. If you then apply the TEXT function to that number, the spreadsheet interprets the number as a date serial value. Because dates in Excel and WPS Spreadsheet start counting from January 1, 1900, numbers 1 through 12 always translate to the first 12 days of January 1900.

Select the target cell

Click on the blank cell where you want the month text to appear.

Enter the formula for abbreviated month

Type `=TEXT(B3, "mmm")` where B3 is your original date cell. This will return the 3-letter month name (e.g., Jan, Feb).

Modify format for full month or year

To get the full month name, change the format code to `=TEXT(B3, "mmmm")`. To include the year, use `=TEXT(B3, "mmm, yyyy")` (e.g., Jan, 2023).

Use the TEXT Function Directly on the Date Cell

Universal Compatibility: This correct formula method works identically and flawlessly in both Microsoft Excel and WPS Spreadsheet.

Free Spreadsheet Software

Convert Dates to Text Easily with WPS Spreadsheet

WPS Spreadsheet provides robust formula support, fully compatible with Excel's TEXT, MONTH, and DATE functions. You can easily format, analyze, and manage your data without any subscription fees.

1. Open your file in WPS: Launch WPS Office and open your spreadsheet document.
2. Select the output cell: Click the empty cell where you want the converted text result to be displayed.
3. Input the TEXT formula: Type `=TEXT(A1, "mmmm")` (replacing A1 with your actual date cell) and press Enter to instantly get the correct month name.

100% compatible with Microsoft Excel formulas and formatting (.xlsx)User-friendly interface for managing complex data conversionsBuilt-in formula error checking and hints to prevent serial date errorsLightweight, free to use, and runs smoothly on all devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel formula return January 1900?

Spreadsheet software stores dates as serial numbers starting from January 1, 1900. If your formula outputs a small number (like 1 through 12 from the MONTH function) and you format it as a date, the software interprets those numbers as the first 12 days of January 1900.

How do I extract both the month and year from a date?

You can extract both by using the TEXT function with the format code 'mmm, yyyy'. For example, typing `=TEXT(A2, "mmm, yyyy")` will convert a date in cell A2 to a readable text string like 'Oct, 2023'.

Is there a way to show the month name without using a formula?

Yes. Select your date cells, right-click, and choose 'Format Cells'. Under the 'Number' tab, select 'Custom' and type 'mmmm' in the Type box. This changes how the date is displayed visually as the month name without altering the underlying date value.

Excel & Data Problems