Function Problems

How to Convert an Excel Date Serial Number to Text

Steve K Oct 10, 2026 869 views

Question details

The user needs to convert an Excel date, stored as a numeric serial value, into a text string while preserving its specific visual format, particularly when combining it with other text.

How to Convert an Excel Date Serial Number to Text with the Displayed Format

Product: Excel
Device & OS: not provided
Scenario: Concatenating a cell containing a date with a text string using formulas without exposing the underlying raw serial number.
Observed behavior: When a date cell is concatenated with text, Excel outputs the underlying serial number (e.g., 45198) instead of the visually formatted date (e.g., 092923).

Before you start

Identify the cell containing your date and decide on the exact date format code you want to display (e.g., "mmddyy", "mm/dd/yyyy", or "dd-mmm-yyyy") before writing your formula.

Solution 1Recommended

Use the TEXT Function to Format Concatenated Dates

The TEXT function is the most effective way to convert a date serial number into text while retaining a specific visual format within a formula.

Excel inherently stores dates as sequential serial numbers for calculation purposes. When you combine a date with text using the ampersand (&) or the CONCATENATE function, Excel strips the cell formatting and displays the raw serial number.

The TEXT function bridges this gap by converting the numeric value into a text string based on the format code you specify, effectively locking in the visual appearance of the date.

Select the target cell

Click on the empty cell where you want the concatenated result to appear.

Type the formula with the TEXT function

Enter your formula starting with your text string, followed by the ampersand (&), and then the TEXT function. For example, type ="The date is "&TEXT(D2,"mmddyy") if your date is in cell D2.

Apply the formula

Press Enter to execute the formula. The date will now appear exactly as formatted in the formula string rather than as a 5-digit serial number.

Use the TEXT Function to Format Concatenated Dates

Customizing Format Codes: You can customize the format string inside the TEXT function to match your needs. Use "dd/mm/yyyy" for standard dates, "mmmm d, yyyy" to spell out the month, or "dddd" to display the day of the week.

Resolve Date Formatting Issues with WPS Office

Easily Manage Excel Functions and Date Formats in WPS Spreadsheet

WPS Spreadsheet fully supports Excel's TEXT function, allowing you to seamlessly convert date serial numbers to text formats. It offers a familiar interface and comprehensive formula capabilities for all your data formatting needs.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates.
2. Select a cell: Click on the target cell where you want to output the combined text and date.
3. Enter the formula: Type the TEXT formula, such as ="Report Date: " & TEXT(A1, "mm/dd/yyyy").
4. View the result: Press Enter to see your correctly formatted text and date combination without exposing serial numbers.

Full compatibility with Microsoft Excel formulas like TEXT and CONCATENATE.Completely free and lightweight alternative to Microsoft Office.Intuitive interface for quickly formatting dates, numbers, and text.Seamless transition with 100% support for .xlsx and .xls file formats.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date turn into a 5-digit number in Excel?

Excel stores dates as sequential serial numbers starting from January 1, 1900. When you remove cell formatting or combine the date with text in a formula, Excel defaults to showing this underlying 5-digit serial number instead of the formatted date.

Can I use the TEXT function to extract just the month or year?

Yes. You can use =TEXT(A1, "mmmm") to extract the full month name as text, or =TEXT(A1, "yyyy") to extract just the 4-digit year from a date serial number.

Does formatting a cell as 'Text' change the serial number?

Simply changing the cell format to 'Text' from the Home ribbon does not change the underlying serial number if the cell already contains a date. You must use the TEXT function or re-enter the date with a leading apostrophe to force Excel to treat it as a true text string.

What are common date format codes for the TEXT function?

Common codes include "mm/dd/yyyy" (e.g., 12/31/2023), "dd-mmm-yy" (e.g., 31-Dec-23), "mmddyy" (e.g., 123123), and "dddd" to display the day of the week (e.g., Sunday).

Excel & Data Problems