Excel TEXT Formula for Dates: Format Dates Clearly

Formulas & functions · Updated November 19, 2026

If A2 contains a real Excel date, use =TEXT(A2,"mmmm d, yyyy") to display it as text such as July 4, 2026. Replace the format pattern in quotation marks to show a different date style; the cell reference supplies the date, and the pattern controls how the result looks.

What the TEXT formula does with dates

Excel stores most dates as numbers and displays them using a date format. The number represents a position in Excel’s date system; the cell’s number format determines whether you see a calendar date, a number, or another presentation. The TEXT function takes a value and a format pattern, then returns a text string. Its general syntax is TEXT(value, format_text).

For the opening example, the layout is a date in A2, such as July 4, 2026. The formula =TEXT(A2,"mmmm d, yyyy") returns the text July 4, 2026. The result looks like a date, but it is text rather than a date value. That distinction matters if you later want to sort dates, calculate elapsed days, or use the result in another formula.

Use TEXT when you need a date’s appearance to become part of a text result—for example, in a sentence, a label, or an export field. If you only want to change how a date looks in its own cell, changing the cell’s number format is usually the better choice.

Common date format codes

The format pattern is a small set of codes, not a list of fixed words. Repeating a code often changes the level of detail. With a date in A2, these examples illustrate common patterns:

  • With the date July 4, 2026 in A2, =TEXT(A2,"m/d/yy") returns 7/4/26.
  • With the same date in A2, =TEXT(A2,"mm/dd/yyyy") returns 07/04/2026.
  • With the same date in A2, =TEXT(A2,"ddd, mmm d") returns Sat, Jul 4.
  • With the same date in A2, =TEXT(A2,"dddd, mmmm d, yyyy") returns Saturday, July 4, 2026.
  • With the same date in A2, =TEXT(A2,"yyyy-mm") returns 2026-07, which can be useful as a year-month label.

In these patterns, d displays the day of the month, while ddd and dddd display abbreviated and full weekday names. m and mm show the month as a number; mmm and mmmm show abbreviated and full month names. yy shows a two-digit year, while yyyy shows four digits. A two-digit year is compact but can be unclear, so use four digits in records where the century should be explicit.

How to build a date label or sentence

TEXT is especially useful when a date needs to sit beside ordinary words. Suppose A2 contains July 4, 2026. With that date in A2, the formula ="Due: "&TEXT(A2,"mmm d, yyyy") returns the text Due: Jul 4, 2026. The ampersand joins text pieces, and TEXT makes sure the date appears in the requested style.

Without TEXT, joining a date cell directly to a label can expose its underlying serial number instead of a readable date. The result also becomes a text string, so it is appropriate for a report heading or message—not for a column that must remain a sortable date field.

You can also join values from multiple cells. If A2 contains a project name and B2 contains a deadline, the layout is project name in A2 and a real date in B2. The formula =A2&" is due "&TEXT(B2,"mmmm d, yyyy") produces a sentence such as Website refresh is due July 4, 2026. The result depends on the text in A2 and date in B2, but the date portion is controlled by the format pattern.

Dates that include a time

Excel date-time values can contain both a date and a time. You can show either part or combine them in one pattern. If A2 contains July 4, 2026 at 3:05 PM, the formula =TEXT(A2,"h:mm AM/PM") returns 3:05 PM. With the same value in A2, =TEXT(A2,"mmm d, yyyy h:mm AM/PM") returns Jul 4, 2026 3:05 PM.

In a date pattern, m means month. In a time pattern, it can mean minutes when it appears next to an hour or seconds code, as in h:mm. This context rule is why a month-and-time pattern should be written deliberately: mmm d, h:mm AM/PM uses the month abbreviation before the day and minutes after the hour.

If you need a 24-hour clock, use an hour code without AM/PM. For example, with the same date-time value in A2, =TEXT(A2,"yyyy-mm-dd hh:mm") returns 2026-07-04 15:05. The leading zero in hh keeps single-digit hours two characters wide.

TEXT versus formatting the cell

Choose the method based on whether you need to change the stored value or only its display:

  • Change appearance only: Keep the date as a date and apply a number format. It remains usable for date arithmetic, chronological sorting, and date-based calculations.
  • Create readable text: Use TEXT when the date is embedded in a sentence, combined with other values, or must be produced in a specific text format.
  • Prepare a text export: Use TEXT when a receiving system expects a particular date string. Check the receiving system’s required format before exporting.

In Excel for Microsoft 365 on Windows, select the date cells and choose a format from Home > Number Format. For more options, open the Format Cells dialog with Ctrl+1 and choose a date format or a custom format. On Mac, Command+1 opens Format Cells. In Excel for the web, use the Number Format control on the Home tab; available controls can differ slightly from desktop Excel.

For example, to show A2 as 4 July 2026 without turning it into text, select A2 and apply a suitable custom date format such as d mmmm yyyy in Format Cells. The cell still contains its original date value. Use TEXT instead if you want the characters “4 July 2026” returned as a formula result.

Text results, sorting, and calculations

A common misconception is that a string that looks like a date is automatically a date. TEXT explicitly returns text. This can affect downstream work: alphabetical sorting of text labels may not follow calendar order, and arithmetic such as subtracting one date from another requires date values rather than labels.

For instance, text dates written as April 10, May 2, and September 1 may sort by the first letters of their month names, not in the same order as a complete timeline across different years. Even a pattern like mm/dd/yyyy is not guaranteed to sort chronologically as text in every system or workflow. Keep the original date column for calculations and sorting, and use a separate TEXT result for display when possible.

If you need a year-month key for sorting, retain an actual date in the data and sort by that date. The text pattern yyyy-mm can be convenient for labels, but it should not be mistaken for a replacement date value. A useful worksheet design is to keep a clean date column and add a separate presentation column only where it helps readers.

When the source is already text

TEXT formats a value; it does not reliably repair every date-looking string. If A2 contains text such as 7/4/2026, Excel may interpret it differently depending on regional settings, or it may leave it as text. Before formatting, check whether Excel recognizes the source as a date—for example, by changing the cell’s number format or using it in a date calculation.

For text dates that need conversion, DATEVALUE can convert a recognizable date string to an Excel date value, but its interpretation depends on the text and regional date conventions. With a date string in A2 that Excel can interpret, the formula =DATEVALUE(A2) returns a date serial that you can format as a date. If an imported value is ambiguous, such as 4/7/2026, confirm whether the source means April 7 or July 4 instead of assuming Excel has chosen correctly.

For data you control, entering dates as actual dates is safer than building them from ambiguous text. You can also use separate year, month, and day fields and construct a date with DATE. If A2 contains the year, B2 the month number, and C2 the day number, the formula =DATE(A2,B2,C2) returns the corresponding date value. Once it is a date, apply a cell format or use TEXT if you specifically need text.

Regional settings and literal text

Month names and some date conventions can reflect the language and regional settings used by Excel or the workbook environment. A format that displays a numeric date consistently may be more suitable for data exchange than a month name, but the receiving system’s specification is the deciding factor. If a formula produces an unexpected month name or date order, check the source value, the format pattern, and the regional settings rather than treating the result as a different date.

To include ordinary words or punctuation in a pattern, place literal text in quotation marks within the format pattern, or build the wording outside TEXT with concatenation. For example, if A2 contains a date, =TEXT(A2,"mmm d, yyyy")&" (deadline)" returns a date label followed by “(deadline).” Keeping the words outside the format pattern with an ampersand is often easier to read and edit.

Frequently Asked Questions

Does TEXT change the date in the original cell?
No. TEXT returns a formatted text result in the formula cell; it does not rewrite the source date in A2. The original date remains available for calculations unless you replace it yourself.

Why does my TEXT formula show a number or an error?
Check that the referenced cell contains a date value Excel recognizes and that the format pattern is in quotation marks. If the source is imported text, convert or clean it first; a date-like string is not necessarily a stored date.

How do I show the full month name?
Use four m characters in the format pattern. With a date in A2, =TEXT(A2,"mmmm") returns the full month name, such as July for a date in July.

Can I use TEXT to show only the weekday?
Yes. With a real date in A2, =TEXT(A2,"dddd") returns the full weekday name. Use ddd for an abbreviated weekday name.

Should I use TEXT or a custom date format?
Use a custom date format when the value should remain a date and only its display needs to change. Use TEXT when the formatted date must become part of a text string or a text output.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.