Formula Errors

How to Display a Date Instead of a Serial Number in Excel Formulas

Muhammad Talha Oct 22, 2026 869 views

Question details

The user wants to display a formatted, readable date instead of a raw numerical serial number when combining text with the TODAY() function in Excel.

How to Display a Date Instead of a Serial Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Concatenating a text string with a dynamic date function like TODAY().
Observed behavior: Excel outputs the date as a raw 5-digit serial number (e.g., "Totals as of 45078") instead of a standard date format.

Before you start

Identify the exact date format you want to display (such as 'm/d/yyyy' or 'dd-mmm-yyyy') before modifying your formula.

Solution 1Recommended

Use the TEXT Function in Your Formula

The most reliable method to solve this issue is converting the date serial number into formatted text directly within your formula.

When Excel combines text and a date function, it defaults to the underlying raw numeric value of the date. The TEXT function forces Excel to apply a specific date format before concatenating it with your text string.

Select the target cell

Click on the cell containing the formula that currently displays the serial number.

Modify the formula

Wrap the date function with the TEXT function. Change your existing formula to: ="Totals as of " & TEXT(TODAY(), "m/d/yyyy").

Apply changes

Press Enter to update the cell. It will now properly display the text combined with the formatted date.

Format Customization: You can replace "m/d/yyyy" with any standard Excel date format string, such as "dd-mmm-yyyy" for an output like 01-Jan-2024.

Solution 2

Apply Custom Cell Formatting

If you want to keep the underlying cell value as a pure date for future calculations, you can use custom cell formatting instead of modifying the formula.

Format Dates and Formulas Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides robust support for Excel functions like TEXT and TODAY(), along with intuitive custom cell formatting to handle date serial numbers without hassle.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
2. Edit the formula: Select the target cell and enter the TEXT function, such as ="Totals as of " & TEXT(TODAY(), "m/d/yyyy").
3. Use custom formatting: Alternatively, right-click the cell, select 'Format Cells', and easily apply your custom text and date display.

Fully compatible with Microsoft Excel formulas and date serial numbersLightweight application that processes large formula arrays instantlyUser-friendly interface for applying custom date and number formats

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show dates as 5-digit numbers?

Excel stores dates as sequential serial numbers to allow for mathematical calculations. By default, January 1, 1900, is stored as serial number 1. When you combine a date with text in a formula without specifying a format, Excel outputs this raw, underlying numeric value.

Can I use the TEXT function to display the day of the week?

Yes. You can use the format code "dddd" within the TEXT function. For example, the formula =TEXT(TODAY(), "dddd") will output the current day, such as 'Monday'.

Does using custom cell formatting change the actual data in the cell?

No, custom cell formatting only modifies the visual representation of the cell. The underlying data remains a date serial number, which allows you to continue using that cell in other mathematical operations, like calculating the difference between two dates.

Excel & Data Problems