To turn an Excel date into formatted text, use =TEXT(A2,"mmmm d, yyyy"), assuming A2 contains a real date; for January 15, 2025, the result is January 15, 2025. TEXT is useful when you need a date in a specific form inside a label or exported string, but it converts the date to text rather than merely changing how a date cell looks.
What the TEXT function does
Excel’s TEXT function converts a numeric value into text according to a number format code. Its syntax is TEXT(value, format_text): the first argument is the value to display, and the second is a format code enclosed in quotation marks. The function works with dates because Excel stores dates as numbers and applies date formats to display them as calendar dates.
The distinction between formatted text and a formatted date matters. If you apply a date format to a cell, its underlying value remains a date that Excel can sort, calculate with, or use in date arithmetic. If you use TEXT, the result is a text string. It may look identical to a date on screen, but Excel will not generally treat it as a date in later calculations.
For example, TEXT is a good fit for creating a sentence such as “Report run: March 8, 2025.” It is usually not the best way to format a column of dates that will be used for filtering, sorting, or calculations. In that situation, keep the date values and format the cells directly.
How Excel stores dates
Excel stores a date as a serial number, with whole-number increments representing days. A time is represented as a fraction of a day. The date format controls how that numeric value appears; it does not change the stored value. This is why two cells can contain the same date serial but display it differently, such as 1/15/2025 or January 15, 2025.
When a date is entered as text, Excel may recognize it and convert it to a date value, but recognition can depend on the computer’s regional settings and the order of day and month. For instance, an entry like 03/04/2025 can be ambiguous across regions. Before using TEXT, confirm that the source cell contains an actual date, not a string that merely resembles one. A recognized date usually behaves as a number in calculations; a text entry may be left-aligned by default, although alignment alone is not a reliable test.
Excel workbooks can use either the 1900 or 1904 date system. That setting affects the serial number behind a date, not the ordinary display pattern produced by a date format. It is mainly relevant when dates are exchanged between workbooks or systems. For most everyday formulas, use the date value in the cell rather than trying to work with its serial number.
Basic date formats with TEXT
In format codes, d controls the day, m controls the month, and y controls the year. Repeating a letter changes the level of detail. With A2 containing the real date January 15, 2025, the following formula returns January 15, 2025:
With the date January 15, 2025 in A2, use =TEXT(A2,"mmmm d, yyyy"). The result is January 15, 2025.
Here are common patterns you can adapt:
"m/d/yy"displays a compact numeric date, such as 1/15/25."mm/dd/yyyy"displays two-digit month and day values, such as 01/15/2025."d-mmm-yyyy"displays a short month name, such as 15-Jan-2025."dddd, mmmm d, yyyy"displays a full weekday and month, such as Wednesday, January 15, 2025."yyyy-mm-dd"gives a year-first form, such as 2025-01-15, which is often convenient for filenames and consistent text exports.
With the date January 15, 2025 in A2, use =TEXT(A2,"ddd, mmm d"). It returns Wed, Jan 15. The format code is a pattern, not a literal description: d gives a one- or two-digit day, dd pads the day with a zero, ddd gives an abbreviated weekday, and dddd gives the full weekday name. Similarly, m gives a numeric month, mm pads it, mmm abbreviates the month name, and mmmm spells it out.
Formatting dates and times together
The same function can display a date and a time in one text result. Common time codes include h or hh for hours, m or mm for minutes, and s or ss for seconds. AM/PM indicates a 12-hour clock; without it, an hour code is normally displayed on a 24-hour basis. The letter m can mean either month or minute, depending on context: beside an hour or before seconds, it is interpreted as minutes.
With a date-time value for March 8, 2025 at 2:05 p.m. in A2, use =TEXT(A2,"mmm d, yyyy h:mm AM/PM"). The result is Mar 8, 2025 2:05 PM. If the source value has no time component, the time portion will appear as midnight. If it has no date component, the date portion may show a date based on the underlying numeric value, so choose a format that matches what the source data actually represents.
Using TEXT in labels and formulas
A common reason to use TEXT is to combine a date with other text. Without explicit formatting, joining a date value to a phrase can produce an unexpected serial number or a display that does not match the desired wording. TEXT makes the intended date representation part of the resulting string.
With a real due date in A2, use ="Due: "&TEXT(A2,"mmmm d, yyyy"). If A2 is January 15, 2025, the result is Due: January 15, 2025. The ampersand joins text, while TEXT formats the date before it is joined. The output is a text string, so it is suitable for a heading, message, or export field, not as a replacement for the original date in calculations.
For a date range, format each endpoint separately and add the separator you want. With the start date in A2 and the end date in B2, use =TEXT(A2,"mmm d")&"–"&TEXT(B2,"mmm d, yyyy"). If the cells contain March 8 and March 12, 2025, respectively, it returns Mar 8–Mar 12, 2025. This compact style is useful in summaries, although it can be unclear if a range crosses a year boundary; include the year on both dates in that case.
TEXT versus cell number formatting
Choose TEXT when the formatted date must become part of a text result. Choose ordinary cell formatting when you want a date to remain a date. For example, to display a date column as 15-Jan-2025, select the cells and use the Number Format options on the Home tab, or open Format Cells with Ctrl+1 on Windows. In Excel for Mac, Command+1 opens Format Cells. Pick a date format or use the Custom category to enter a pattern.
Cell formatting is generally preferable for a data table because the underlying values remain usable in date calculations, chronological sorting, and date filters. TEXT is more appropriate for report labels, concatenated messages, or a fixed-format text field. One trade-off is that a TEXT result may sort alphabetically rather than chronologically. For instance, text dates beginning with month names do not necessarily sort in calendar order. A year-month-day pattern sorts more sensibly as text, but retaining actual date values is still safer for spreadsheet analysis.
Changing the display format does not change the date value, and it is reversible. Converting to text with TEXT creates a new result; the original date remains unchanged if it is in another cell, but downstream formulas that refer to the TEXT result receive text.
Regional settings and format codes
Month and weekday names are affected by language and regional settings, so a code that produces English month names in one setup may produce localized names in another. A numeric pattern such as yyyy-mm-dd avoids language-specific month names and is often a practical choice for machine-readable output. It does not, by itself, guarantee that every receiving system will interpret the text as a date; import rules and the target application still matter.
Be cautious with slash-separated numeric dates such as 03/04/2025. They are compact but can be read as March 4 or April 3 depending on regional convention. If a person will read the result, spelling out the month removes that ambiguity. If another program will consume it, agree on the expected format and test an import rather than assuming that a displayed string will be parsed correctly.
Format codes are not always identical across spreadsheet applications or locales. If a code is rejected or produces a different result, use the format options in your version of Excel to inspect available date patterns. Also note that using quoted text inside a format code is different from using a formula string: the entire format_text argument must be inside formula quotation marks, and any literal punctuation belongs within that format string.
Common problems and how to fix them
- The result is a number or an unexpected date. Check whether the input is a real date and whether the chosen format code is what you intended. A serial number may indicate that the source is numeric but the cell is being displayed with a general format elsewhere.
- The formula returns #VALUE!. The source may be text that Excel cannot interpret as a number or date. Convert or clean the input first, and verify that it is a valid date value before applying TEXT.
- The formula displays the formula itself. Confirm that the cell is not formatted as Text, then re-enter the formula. Also check that the entry begins with an equals sign and that Show Formulas has not been enabled.
- The month and day seem reversed. This usually points to an input interpretation or regional-settings issue, not a flaw in TEXT. Verify the underlying date rather than changing the output pattern blindly.
- The date no longer sorts correctly. TEXT returns text, which may sort alphabetically. Keep a separate original date column for sorting and calculations, and use the text version only where formatted output is needed.
- The month code shows minutes. In a date-time format, m can mean minutes when it is next to hour or second codes. Check the surrounding codes and use a month pattern in a date-only section when appropriate.
A useful troubleshooting habit is to test the input cell on its own: temporarily apply a standard date format to see whether Excel recognizes it as a date. If it still displays as the original text, clean or convert the source before building a formatted string. Keep the original values intact until the results are verified, especially when the workbook is used for reporting or data exchange.
Frequently Asked Questions
How do I convert a date to text in Excel?
Use TEXT with the date cell and the desired format code. For example, with a real date in A2, =TEXT(A2,"dd-mmm-yyyy") returns a text result such as 15-Jan-2025.
Does TEXT change the original date?
No. The formula returns a formatted text result in its own cell; it does not modify the source cell. However, formulas that use the TEXT result get text rather than the original date value.
How do I show a month name with TEXT?
Use mmm for an abbreviated month or mmmm for the full month name. With January 15, 2025 in A2, =TEXT(A2,"mmm") returns Jan and =TEXT(A2,"mmmm") returns January.
Why does Excel show a date as a number?
Dates are stored as serial numbers. Apply a date format to display the value as a calendar date. If the number appears inside a text formula, use TEXT around the date value and specify the desired format.
Can I use TEXT for dates that I need to sort?
You can, but the result is text and may not sort chronologically. Keep the real date values for sorting, filtering, and calculations; use TEXT only for presentation or a required text export.
Choosing the right approach
Use TEXT when a date needs to be embedded in a string or written in a precise text format. Use number formatting when the cell should remain a date that Excel can calculate with. Before relying on a TEXT result, confirm the source is a real date, pick an unambiguous pattern, and consider whether the destination expects text or a date value. That small distinction prevents many confusing display and sorting problems.
Related
- Excel Shortcut to Select an Entire Column
- Excel Shortcut to Select to the End of a Column Explained
- Excel Shortcut to Refresh a PivotTable and All Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculating Due Dates in Excel With Simple FormulasTo calculate a due date in Excel, add the number of calendar days to a valid start date:…
- Excel FILTER Function: Return Results Without DuplicatesExcel’s FILTER function does not remove duplicates by itself. To return matching results without repeats, wrap FILTER inside…
- How to Filter Strikethrough Data in ExcelExcel’s regular filter menu can’t filter rows by strikethrough formatting. To show those rows, add a helper column…
- Excel SUMIF: Sum Columns When a Cell Contains TextTo add values in one column only when the corresponding cell in another column is exactly x, use…
- COUNTIF in Excel: Examples and How to Use It Step by StepTo count cells that match one condition in Excel, use =COUNTIF(A2:A11,"Apple"). With fruit names in A2:A11, this returns…
- Excel VLOOKUP: Find Data Across TablesIn Excel, VLOOKUP searches for a value in the leftmost column of a range and returns a value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.