Formula Errors

Fix Excel TEXT Formulas Showing Date Serial Numbers Instead of Weekdays

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 868 views

Question details

The user needs Excel to display weekday names consistently with =TEXT(C1,"dddd") when some cells instead show date serial numbers.

Product
Microsoft Excel
Device & OS
not provided
Scenario
Converting dates into full weekday names using the TEXT function.
Observed behavior
Some result cells display date serial numbers even though their formulas appear identical to formulas that return weekday names.
Before you start

Select an affected result cell and check its exact formula and source-cell reference in the formula bar. Excel stores dates as numbers, but =TEXT(C1,"dddd") should return a weekday name as text when C1 contains a valid date value.

Solution 1Recommended

Verify the TEXT formula and force a full recalculation

Use this approach when you need weekday names as text and some formula results appear stale or inconsistent.

1
Check the actual formula

Click an affected result cell and inspect the formula bar. Confirm that it contains =TEXT(C1,"dddd"), replacing C1 with the intended date cell. A formula such as =C1 returns the underlying date value rather than a weekday name.

2
Test the source date

In an unused cell, enter =ISNUMBER(C1), using the same source reference as the affected formula. TRUE means the source is numeric, as Excel dates normally are; it does not by itself prove that the number represents the intended date. Select the source cell and inspect its value and date display.

3
Re-enter the formula in a test cell

Select an unused cell, set Home > Number Format to General, and enter =TEXT(C1,"dddd"). If this returns the expected weekday, compare its source reference and formula with the affected cell.

4
Recalculate the workbook

Open Formulas > Calculation Options and select Automatic. In Excel for Windows, press Ctrl+Alt+F9 to force a full recalculation of all open workbooks. If results remain stale, save the workbook, close Excel, and reopen it.

TEXT results are not numeric dates: A correctly calculated TEXT formula returns text. A numeric cell format alone should not turn that weekday text into a date serial number, so verify the actual formula, reference, and calculation state before changing formats.
Weekday formatting in WPS Spreadsheet

Display Weekday Names from Dates in WPS Spreadsheet

WPS Spreadsheet can convert numeric dates into weekday text with the TEXT function or display weekdays with a custom number format. These methods accomplish the same date-display task without relying on Excel's recalculation shortcut.

  1. 1. Open your workbook: Launch WPS Spreadsheet and choose Open to select your Excel workbook.
  2. 2. Check the source value: Select an unused cell and enter =ISNUMBER(C1), replacing C1 with your source date cell. If the result is FALSE, inspect the source and enter a valid date before continuing.
  3. 3. Create weekday text: Click a blank result cell, enter =TEXT(C1,"dddd"), and press Enter. Use the fill handle to copy the formula down for additional dates.
  4. 4. Use formatting if you need numeric dates: Instead of using TEXT, select the numeric date cells, right-click, and choose Format Cells > Number > Custom. Enter dddd and confirm to display weekday names without changing the stored dates.
Convert dates into full weekday names with =TEXT(C1,"dddd").Keep numeric dates intact by applying a custom dddd format.Open and save Microsoft Excel .xlsx and .xls files.Use familiar spreadsheet formulas and formatting controls.
QA img-9

Frequently Asked Questions

Why does Excel show a number when I reference a date cell?

Excel stores dates as serial numbers. A formula such as =C1 may show that number if the result cell uses General or Number formatting. To show a weekday, use =TEXT(C1,"dddd") or apply dddd through Format Cells > Number > Custom.

Can changing the number format fix a TEXT formula that shows a serial number?

Not normally. TEXT returns text, so numeric formatting should not convert its weekday result into a serial number. Select the cell, inspect the formula bar, confirm the reference, and force a recalculation. Apply a custom dddd format only to numeric dates or formulas that return numeric dates.

What does Ctrl+Alt+F9 do in Excel?

In Excel for Windows, Ctrl+Alt+F9 recalculates all formulas in all open workbooks, even if Excel does not mark them as needing calculation. Also check Formulas > Calculation Options > Automatic so subsequent changes update normally.

How can I tell whether my source date is stored as text?

Enter =ISNUMBER(C1) in an unused cell, replacing C1 with the source reference. FALSE means the value is not numeric. If it is a date stored as text, re-enter it as a date Excel recognizes or use Data > Text to Columns with the appropriate date order, then test the TEXT formula again.

Should I use TEXT or a custom dddd format?

Use =TEXT(C1,"dddd") when you need a weekday name as text, such as a label for combining with other text. Use Format Cells > Number > Custom > dddd when you want the cell to remain a numeric date for calculations and date-based sorting.