Function Problems

Fix Excel TEXT Formula Returning the Wrong Date Format

Chanuka Geekiyanage Oct 10, 2026 870 views

Question details

The user needs to display a date cell in a specific month and year format, but the TEXT formula is returning incorrect results.

Fix Excel TEXT Formula Returning the Wrong Date Format

Product: Excel
Device & OS: not provided
Scenario: Trying to format a cell containing a date and time value (e.g., 09/28/2023 10:53 AM) to display only the month and year (e.g., 09/2023).
Observed behavior: The TEXT formula fails to output the expected month and year format, likely because the source data is stored as a text string rather than a valid date, or due to regional settings interference.

Before you start

Before applying any formulas, check whether your date cell is stored as a true Excel date value or as a text string, as this dictates which solution will work.

Solution 1Recommended

Use the TEXT Formula for Valid Excel Dates

If your cell is recognized as a true date by Excel, the TEXT function is the fastest way to extract the month and year.

Excel handles dates as serial numbers. As long as the data is recognized as a valid date, the TEXT formula will smoothly convert it into any desired string format.

Select the target cell

Click on the empty cell where you want the new formatted date to appear.

Enter the TEXT formula

Type the formula =TEXT(B1, "mm-yyyy") or =TEXT(B1, "mm/yyyy") depending on your preferred delimiter, replacing B1 with your actual date cell.

Apply the formatting

Press Enter to retrieve the correctly formatted month and year.

Use the TEXT Formula for Valid Excel Dates

Quick Verification: True Excel dates are right-aligned by default. If your date is left-aligned, it might be stored as text, and you should use the extraction method below.

Solution 2

Extract Date Components Using Text Functions

If the original date is stored as a text string, standard date formulas won't work, requiring you to manually extract characters.

Advanced Spreadsheet Tool

Fix Date Formatting Instantly in WPS Spreadsheet

WPS Office offers a powerful and fully compatible spreadsheet tool where functions like TEXT, LEFT, and MID work identically to Microsoft Excel. Download it for free to manage your data and fix date errors effortlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office, click on 'Spreadsheet', and open your existing workbook.
2. Apply the TEXT function: Click the target cell and enter =TEXT(B1, "mm/yyyy") to perfectly format real dates.
3. Use text-extraction if needed: If the source date is text, simply type =LEFT(B1,2)&"/"&MID(B1,7,4) to bypass format errors.

Fully compatible with Microsoft Office Excel (.xlsx) formats and advanced formulas.Identical date-handling capabilities and TEXT function syntax.Built-in Text to Columns tool to instantly fix dates mistakenly stored as text.Free, lightweight, and features a familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

How can I easily tell if my date is stored as text or a real date?

By default, Excel right-aligns true numbers and dates, whereas it left-aligns text. You can also use the formula =ISNUMBER(B1); if it returns TRUE, the cell contains a real date. If it returns FALSE, it is stored as text.

Why does my TEXT formula output the exact formula text instead of the actual result?

Your target cell is likely formatted as 'Text'. To fix this, select the cell, press Ctrl+1 to open the Format Cells dialog, change the format category to 'General', click OK, and then hit Enter in the formula bar to recalculate.

Can my computer's regional settings cause date formula errors?

Yes. If your system's regional settings use a different date structure (such as DD/MM/YYYY instead of MM/DD/YYYY), Excel might misinterpret the source date. This mismatch can cause valid dates to be read as text, making the TEXT function fail or return incorrect values.

Excel & Data Problems