To convert an Excel date number into readable text, use the TEXT function with a date format, such as =TEXT(A2,"mmmm d, yyyy"). If A2 contains the date January 1, 2024, the formula returns the text “January 1, 2024”; applying a date format to the cell alone changes how the value looks, not its underlying type.
What an Excel date number represents
Excel usually stores a date as a serial number: a count of days from a starting point, with any time recorded as a decimal fraction of a day. That is why a cell can display “Jan 1, 2024” while the formula bar or an unformatted cell shows a number such as 45292. The number is not an error; it is Excel’s internal representation of the date.
In the standard 1900 date system used by most Windows workbooks, serial 1 corresponds to January 1, 1900. Excel also preserves a historical compatibility quirk: it treats 1900 as if it had a February 29, even though that date did not exist. As a result, serial 60 represents that nonexistent date, and serial 61 represents March 1, 1900. This mainly matters when working with very old dates or comparing data from different systems.
Some workbooks use the 1904 date system, historically associated with older Mac workbooks. The same calendar date has a serial number that differs by 1,462 days between the two systems. Excel can usually adjust dates when copying between workbooks, but a raw serial copied or imported as a number may not carry enough context. When dates look shifted by several years, check the workbook’s date system under File > Options > Advanced, in the “When calculating this workbook” section.
Format a date or convert it to text?
First decide whether you need a date that merely looks different or an actual text string. This distinction affects sorting, calculations, filtering, and exporting. A real Excel date can be formatted in many ways while remaining a numeric value that formulas can use for date arithmetic.
- Change the display: Select the date cells, press Ctrl+1 on Windows, choose the Number tab, select Date or Custom, and choose or enter a format. On Mac, Command+1 opens Format Cells. This keeps the underlying values as dates.
- Create text: Use
TEXTin another cell. Its result is text, which is useful for labels, reports, or systems that require a particular written date format.
For example, formatting a serial with the custom format yyyy-mm-dd may display 2024-01-01, but the cell still contains a date number. By contrast, TEXT returns characters. The displayed results can look identical, so test the underlying value if a later formula behaves unexpectedly. Formatting is generally preferable for normal spreadsheet work; conversion is appropriate when the value must become text.
Use TEXT to choose the written date
The TEXT function takes a value and a format code. With a date serial in A2, the following formula creates a long, readable date:
With the date January 1, 2024, in A2, this formula returns the text January 1, 2024:
=TEXT(A2,"mmmm d, yyyy")
Excel’s date format codes determine what appears in the result. Common choices include:
ddisplays the day without a leading zero;dddisplays it with one.dddgives an abbreviated weekday, whileddddgives the full weekday name.mdisplays the month number without a leading zero;mmadds one.mmmgives an abbreviated month name, andmmmmgives the full name.yydisplays a two-digit year;yyyydisplays four digits.
With January 1, 2024, in A2, this formula returns the text 2024-01-01, a common year-month-day style:
=TEXT(A2,"yyyy-mm-dd")
That layout is often convenient for filenames and data exchanges because the year appears first. It is still text, however, not a date value. A format code such as mmm d, yyyy produces a compact English-style label, while dddd, mmmm d, yyyy includes the weekday. The best code depends on the reader and the destination, not just on what fits in the cell.
Include time or add text around a date
Because Excel stores time as a fraction of a day, the same function can display a date and time together. For instance, a value with a whole-number date portion and a half-day fraction represents noon. Include hour and minute codes when the time matters.
With the date and time January 1, 2024 at 12:00 PM in A2, this formula returns the text 2024-01-01 12:00 PM:
=TEXT(A2,"yyyy-mm-dd h:mm AM/PM")
Be attentive to m: it can mean month in a date format or minute in a time format. In a date-and-time code, Excel interprets it as minutes when it is used alongside hour or seconds codes; otherwise, it usually means month. If a result shows the wrong component, simplify the format code and check its position around the time codes.
You can combine a formatted date with ordinary text to create a heading or sentence. With January 1, 2024, in A2, this formula returns the text Report date: Jan 1, 2024:
="Report date: "&TEXT(A2,"mmm d, yyyy")
Once combined with text, the result is a label, not a date suitable for arithmetic. Keep the original date in a separate cell if the workbook will still need to calculate days, sort chronologically, or apply date filters.
Convert text that looks like a date
A common related problem is the reverse: a cell contains characters that look like a date, but Excel does not recognize them as dates. Applying a date format to such text may have no effect. The way to convert it depends on the text’s pattern and the computer’s regional date settings.
If A2 contains the unambiguous text 2024-01-02 and Excel recognizes that pattern, a conversion formula can use DATEVALUE. With A2 containing the text 1/2/2024 on a system that interprets dates as month/day/year, this formula returns the date serial 45293 (January 2, 2024):
=DATEVALUE(A2)
The result is a number representing a date; apply a date format to display it as desired. The example is deliberately tied to a regional interpretation: on a day/month/year system, 1/2/2024 may mean February 1 instead. If the source is ambiguous, do not rely on automatic parsing. Identify the intended order and build the date from its year, month, and day parts, or use a controlled import process.
For a date whose components are known, the DATE function avoids ambiguity. With the year in A2, month in B2, and day in C2, where those cells contain 2024, 1, and 2, this formula returns a real date for January 2, 2024:
=DATE(A2,B2,C2)
You can then apply a date number format, or wrap the result in TEXT if a text output is specifically needed. When importing a column, the Data > Text to Columns workflow can also help specify how text dates should be interpreted. Review the preview and the import settings rather than assuming Excel will infer the intended order correctly.
Why a date formula may show a number or text
Several symptoms can make a date conversion look broken even when the values are valid. The fix depends on whether Excel sees a number, a date, or text:
- A large number appears: The cell may be displaying the serial without a date format. Apply a date format, or use
TEXTif you truly need text. - The formula appears instead of its result: The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell to General, re-enter the formula, and check Formulas > Show Formulas.
#VALUE!appears: The input may be text Excel cannot interpret as a date, or it may contain extra characters. Inspect the source and its date order.- The date is shifted: Check whether the source workbook uses the 1904 date system, and check whether a text import interpreted month and day in the wrong order.
- The output has unexpected month or minute details: Review the date/time format code, especially the use of
mnear hour codes.
Also check whether a formula result is being treated as text later in the workbook. A TEXT result may look like a date but will not behave like a date in ordinary date calculations. For example, sorting text dates in a month-day-year layout can produce an alphabetical order rather than chronological order. Retain a date-valued column for calculations and use a separate text column for presentation when both roles are needed.
Choose a format for sharing and exporting
For people reading the workbook, choose a format that is clear in their region. A date such as 03/04/2024 is ambiguous: it can mean March 4 or April 3. A four-digit-year format such as 2024-03-04 is less ambiguous to readers familiar with the year-month-day convention. Month names, such as 4 Mar 2024, can be clearer in a human-facing report, though the language of the month may depend on Excel’s regional settings.
For calculations, preserve real dates and use number formatting. For an export that explicitly requires text, use TEXT and verify the output in the receiving application. A workbook’s display format does not always guarantee that another program will interpret a date the same way. CSV files contain text fields rather than Excel cell formatting, and date interpretation on reopening can depend on regional settings and import choices.
If you need to replace formulas with their text results, copy the formula cells and use Home > Paste > Paste Values. This removes the formulas and leaves their displayed text results as values. Keep a backup or preserve the source date column first: after replacement, the text no longer updates if the original date changes. On Excel for the web, the same general distinction between a number format and a text result applies, although the ribbon layout can differ slightly from the Windows desktop app.
Frequently Asked Questions
How do I convert an Excel date number to text?
Use TEXT with the cell and a format code, such as =TEXT(A2,"mmmm d, yyyy"). It returns a text string. Replace A2 with the cell containing your date serial, and change the format code to suit the destination.
How do I change a date number without converting it to text?
Select the cells, press Ctrl+1 on Windows (Command+1 on Mac), and choose a date format or enter a custom one. This changes the display while retaining a real date value for calculations.
Why does Excel show a number instead of a date?
Excel may be displaying the underlying date serial because the cell has General or Number formatting. Apply a date format. If the value is text rather than a serial, formatting alone will not convert it; parse the text into a date first.
Can I turn a date into text in the format YYYY-MM-DD?
Yes. If A2 contains a date, use =TEXT(A2,"yyyy-mm-dd"). The result is text in that layout, so keep a separate date value if you need to do calculations or chronological sorting.
Why does DATEVALUE interpret my date incorrectly?DATEVALUE reads text according to the date patterns and regional settings Excel recognizes. A value such as 1/2/2024 is ambiguous across regions. Use an unambiguous source format or separate year, month, and day values and construct the date explicitly.
Related
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
- Excel Conditional Formatting for Upcoming Dates
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make Bullet Points in Two Columns in WordTo make bullet points appear in two columns in Microsoft Word, place the list in its own section,…
- Box Plot in think-cell: What Works and How to Build Onethink-cell does not provide a dedicated box-and-whisker chart in its standard chart menu. In PowerPoint for Microsoft 365…
- Excel Conditional Formatting Arrows for Showing ChangeExcel’s conditional-formatting arrows can show whether values are relatively high or low, but they do not automatically calculate…
- Excel File Won’t Open in Protected View? FixesIf an Excel file will not open in Protected View, first confirm that the file came from a…
- How to Calculate Hours Worked in ExcelTo calculate hours worked in Excel, enter a shift’s start time in B2, end time in C2, and…
- Add a Watermark to Just the First Page in WordTo add a watermark to just the first page in Word, separate page one from the rest of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.