Excel TEXT Function for Dates: Format Dates as Text

Formulas & functions · Updated November 20, 2026

Use Excel’s TEXT function to display a date in a chosen written format: if A2 contains the date June 7, 2026, enter =TEXT(A2,"mmmm d, yyyy") to return June 7, 2026 as text. The function changes how the value is shown in its result, not the original date in A2.

How the TEXT function works

TEXT takes a value and a format code, then returns a text string. Its general structure is =TEXT(value,format_text). The first argument is the value you want to display; the second, in quotation marks, describes the desired appearance. For dates, that value should normally be a real Excel date, not a string that only looks like one.

For example, with a real date in A2 representing June 7, 2026, =TEXT(A2,"mmmm d, yyyy") returns the text June 7, 2026. The formula does not change A2, and its result is not a date value that Excel can sort or calculate with in the same way. That distinction matters when you use the result in later formulas or export it to another system.

Format codes resemble the codes used in Excel’s custom number formats. Common date components include d for the day, m for the month, and y for the year. Repeating a letter usually changes the detail shown: d can show a day number, while dddd shows the full weekday name. The letters and punctuation go inside the format string’s quotation marks.

Why Excel dates need a format

Excel generally stores dates as serial numbers: a count of days from a starting point in the workbook’s date system. Times are represented as fractions of a day. A cell might therefore contain a numeric date value even though Excel displays it as June 7, 2026, or as 6/7/2026. The displayed style is controlled by a number format.

The TEXT function applies a format while producing a text result. This is useful when a date needs to appear inside a sentence, in a label, or in a consistently shaped export field. It is different from merely changing a cell’s display format: that changes the appearance of the date while keeping the underlying value numeric.

Most Windows workbooks use Excel’s 1900 date system, while workbooks can also use the 1904 date system. Those systems assign different serial numbers to dates, so moving raw date serials between workbooks can require care. In ordinary work, you usually do not need to think about the serial number; just make sure the source cell contains a valid date and choose a suitable format.

Choose a date format code

Pick a format based on who will read the result and what the text will be used for. Month names are unambiguous across many date-order conventions, while a short numeric date such as 6/7/26 can mean different things in different regions. A year-first pattern such as 2026-06-07 is often useful for labels and data exchange, though a text result still is not a true date field.

  • d shows the day without a leading zero; dd adds one, as in 07.
  • ddd shows an abbreviated weekday, and dddd shows the full weekday name.
  • m shows the month number; mm adds a leading zero.
  • mmm shows an abbreviated month name, and mmmm shows the full name.
  • yy shows a two-digit year; yyyy shows the four-digit year.

With June 7, 2026 in A2, =TEXT(A2,"ddd, mmm d") returns Sun, Jun 7. With the same date in A2, =TEXT(A2,"yyyy-mm-dd") returns 2026-06-07. With the same date in A2, =TEXT(A2,"mm/dd/yy") returns 06/07/26. These are text strings, even though their characters look like familiar date formats.

Excel interprets m as a month in a date context and as minutes in a time context. If a format contains both dates and times, use a clear pattern such as h:mm for hours and minutes. With a date-and-time value in A2 representing June 7, 2026 at 2:30 PM, =TEXT(A2,"mmm d, yyyy h:mm AM/PM") returns Jun 7, 2026 2:30 PM.

Practical formulas for dates

For a report heading, adding a formatted date to ordinary text creates one readable label. With a real report date in A2, ="Report date: "&TEXT(A2,"mmmm d, yyyy") returns Report date: June 7, 2026. The ampersand joins the fixed words to the formatted date. Because the entire result is text, it is convenient for headings but unsuitable as a date for date arithmetic.

For a month-only label, with a real date in A2, =TEXT(A2,"mmmm yyyy") returns June 2026. You can use this in a chart label or a summary heading. Be aware that if you sort a column containing month names or month-year labels as text, Excel may sort alphabetically rather than chronologically. Keep a separate real date or month-start value for sorting when order matters.

For a date range label, with the start date in A2 and end date in B2, =TEXT(A2,"mmm d")&"–"&TEXT(B2,"mmm d, yyyy") can return Jun 7–Jun 12, 2026. This is a display string, not a pair of dates or a range that Excel can interpret for calculations. If the dates cross years, consider including the year on both ends so the label cannot be misread.

TEXT versus formatting a cell

Use TEXT when you need a formatted date inside a formula result or need the result as text. Use ordinary cell formatting when you only want a date to look different on the worksheet and still need it to behave as a date. For example, a genuine date can be displayed as June 7, 2026 by selecting the cell and choosing an appropriate date format; the underlying date value remains available for sorting, filtering, and calculations.

In Excel for Microsoft 365 on Windows, select the date cell and press Ctrl+1 to open Format Cells, then choose the Number tab and a Date category or Custom format. You can also use the number format controls on the Home tab. In Excel for Mac, Command+1 opens Format Cells; the ribbon’s number format controls are also available. Excel for the web offers number-format controls on the Home tab, though the dialog and available controls may differ from the desktop versions.

A useful rule is to retain dates as dates in your data table and create a separate text label only where presentation requires it. That avoids losing date-based sorting and calculations just to get a preferred appearance. If a workbook will be used for analysis, do not replace a date column with the output of TEXT unless text is genuinely the required data type.

When the source date is stored as text

A cell that looks like a date may actually contain text imported from a file or typed in a format Excel does not recognize. Applying TEXT to that value may return an error or an unexpected result because Excel does not have a date serial to format. Check whether the cell behaves like a number: changing its number format or using it in date calculations can help reveal whether the value is a real date.

If the text is a date string Excel recognizes under the computer’s regional settings, DATEVALUE can convert it to a date value. With recognizable date text in A2, =TEXT(DATEVALUE(A2),"mmmm d, yyyy") returns a formatted text date. For example, if A2 contains text that Excel interprets as June 7, 2026, the result is June 7, 2026. Whether a string such as 06/07/2026 is interpreted as June 7 or July 6 can depend on regional settings, so inspect the source convention rather than assuming.

For data with a known but unusual structure, it may be safer to convert the components deliberately instead of relying on an ambiguous date string. Check the imported values, the workbook’s regional context, and the result on several rows before filling a conversion formula down a large dataset. Do not treat a successful-looking display as proof that every source date was interpreted correctly.

Common problems and how to avoid them

  • The result is a number or an error: Check that the first argument points to a valid date value and that the format string is enclosed in straight quotation marks. A text value that merely resembles a date may need conversion first.
  • The month appears where minutes were expected: In time formats, m can mean minutes when it follows an hour code or precedes seconds. Use a clear date-and-time pattern and inspect a known example.
  • The output sorts in the wrong order: TEXT returns text. Sort by a real date column, or preserve a separate date value for chronological sorting.
  • A numeric-looking date is ambiguous: Formats such as 03/04/2026 depend on date-order conventions. Use a month name or an explicitly agreed year-month-day display when readers may be in different regions.
  • The date does not update: Confirm that the formula references the intended source cell and that calculation is not set to Manual. The displayed text changes when the referenced date changes and the formula recalculates.
  • Words in the format look like codes: Literal text in a custom format may need quotation marks inside the format string. For simpler labels, joining fixed text with & outside the TEXT function can be easier to read and maintain.

Also check whether a date includes a time component. Formatting a date-time value with a date-only pattern hides the time in the text output; it does not remove the time from the original cell. Conversely, including an hour code can reveal a time that was not obvious in the worksheet’s current display.

Language, sharing, and export considerations

Month and weekday names are affected by Excel’s language and regional settings. A formula that displays a month name on one computer may not produce the same localized wording on another computer with different settings. If a workbook will be shared internationally, test the result in the target environment and consider whether numeric, language-neutral labels are more appropriate for the task.

For data intended for another application, agree on the format with the recipient. A text date can be useful when a receiving system expects a fixed string, but it may be parsed differently or treated as plain text. When the receiving system supports real date fields, passing a genuine date value is generally more flexible. When it requires text, use an explicit, consistent format and verify a sample import rather than assuming that appearance guarantees interpretation.

Excel for Windows, Mac, and the web supports the TEXT function, though interface details and regional settings can vary. The formula’s comma separators shown here are standard for many installations; some regional configurations use semicolons as formula argument separators. If Excel rejects a formula copied from this page, check the list separator expected by your installation and adjust it without changing the function’s logic.

Frequently Asked Questions

Does TEXT change a date into text?
Yes. The function returns text displaying the value according to the format code. It does not convert the original source cell or preserve the result as a date value.

How do I show a date as month name and year?
Use a format code with the full month and four-digit year. With a real date in A2, =TEXT(A2,"mmmm yyyy") displays a result such as June 2026. The result is text.

Can I use a TEXT-formatted date in a calculation?
Not reliably as a date value. Keep the original date in a separate cell for date arithmetic, comparisons, and chronological sorting. Use the text result for presentation, such as a heading or a message.

Why does TEXT show the wrong month or day?
The source may be stored as text, or Excel may have interpreted an ambiguous imported date using a different regional convention than you expected. Check the underlying value and the source date order before changing the display code.

Can I combine a date with other words in a formula?
Yes. Format the date with TEXT, then join it to fixed words with the ampersand operator. The complete result is text, so keep a separate date value if you need to sort or calculate by date.

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.