Excel Date Format Formulas: TEXT, DATEVALUE, and More

Formulas & functions · Updated November 18, 2026

To display a date as text with an Excel formula, use TEXT; with a date in A2, =TEXT(A2,"mmmm d, yyyy") returns a label such as March 15, 2025. If you only want to change how a real date looks in a cell, apply a number format instead—the date remains usable for calculations.

Formula or cell format: which should you use?

The right choice depends on what you need the result to do. Excel stores most dates as serial numbers and applies a display format to show those numbers as dates. Changing that format affects appearance, not the underlying date value. A date formatted as March 15, 2025 can still be sorted chronologically, subtracted from another date, or used in a date calculation.

The TEXT function does something different: it converts a number or date into text arranged according to a format code. That is useful when building a sentence, preparing a label, or creating a date string for export. But text that looks like a date is not necessarily a date value. You may need to convert it back before doing date arithmetic.

  • Choose cell formatting when the cell should remain a genuine date for sorting, filtering, calculations, or charts.
  • Choose TEXT when you specifically need formatted characters as part of a text result.

How Excel stores and displays dates

In the standard 1900 date system used by most Windows workbooks, Excel represents dates with serial numbers: whole-number increments represent days, and a fractional part can represent time. The number format tells Excel how to display that value. For example, the same underlying date can appear as 3/15/2025, 15-Mar-2025, or Saturday, March 15, 2025, depending on the selected format.

This separation explains a common surprise: a cell may show a number such as 45730 instead of a date. The value may still be a valid date serial; the cell may simply be formatted as General or Number. Applying a date format changes what is shown. If the value is actually text, however, changing the format alone may have no effect.

Excel also supports a 1904 date system, used by some older workbooks and available as a workbook setting. The same calendar date has a different serial number in that system, so copying raw date serials between workbooks that use different systems can shift dates. Most everyday users do not need to change the setting, but it is worth checking when dates move by several years after a workbook transfer.

Format dates without a formula

For ordinary date display, format the cell rather than wrapping its value in a formula. In Excel for Microsoft 365 on Windows, select the date cells and choose a format from Home > Number Format. For more control, press Ctrl+1 to open Format Cells, select the Number tab, and choose Date or Custom. On a Mac, Command+1 opens Format Cells; the ribbon’s number-format controls are also available, though their placement can vary with the Excel window and ribbon layout.

In the Custom category, enter a format code such as yyyy-mm-dd or d mmm yyyy. The first displays a date like 2025-03-15; the second displays it like 15 Mar 2025. Custom formats are particularly helpful when you want a consistent display without changing the workbook’s stored date values. Applying a format to a range also makes dates easier to scan without needing an extra helper column.

Excel’s built-in date choices may reflect the computer’s regional settings. If the available short-date format uses month/day/year but you need day/month/year, choose a suitable built-in option or define a custom format. This changes presentation; it does not resolve ambiguity in text that has already been entered or imported.

Using TEXT to return a formatted date

Use TEXT when the formatted date must become part of a text result. With a genuine date in A2, =TEXT(A2,"dd-mmm-yyyy") returns text such as 15-Mar-2025. With a date in A2, =TEXT(A2,"dddd, mmmm d, yyyy") returns a longer label such as Saturday, March 15, 2025. The result is text, even though the original value is a date.

TEXT can combine a date with other words. With a date in A2, =TEXT(A2,"mmmm d")&" report" returns text such as March 15 report. This can be convenient for headings or a generated description, but it is less suitable as a replacement for the date in a data table. A text label may sort alphabetically rather than in calendar order and usually cannot be used directly in date arithmetic.

The format code inside TEXT follows Excel’s number-format conventions. Common date tokens include d for day, m for month, and y for year. Repeating a token changes the detail: dd adds a leading zero to a one-digit day, mmm uses an abbreviated month name, and mmmm uses the full month name. A four-character year code displays four year digits.

Be careful with m: Excel also uses it for minutes in time formats. In a date-and-time code, its meaning depends on context; near hour or second tokens it can represent minutes. If a format shows an unexpected month or minute, inspect the surrounding time tokens and test the format on a single cell.

Building dates with DATE

When a year, month, and day are stored separately, DATE creates a genuine Excel date from those parts. Suppose B2 contains 2025, C2 contains 3, and D2 contains 15. In that layout, =DATE(B2,C2,D2) returns the date March 15, 2025. Its displayed appearance depends on the result cell’s number format, so format the formula cell as a date if it initially shows a serial number.

DATE is also useful when source values are split across columns or when you need a reliable date value for later calculations. It interprets the arguments as year, month, and day, in that order—not the order used by a regional short-date display. It can normalize out-of-range month or day values into a later or earlier date, so check that source components are valid if unexpected results would be a problem.

For example, a month value beyond 12 rolls into the following year rather than being treated as a literal month name. That behavior can help with intentional date arithmetic, but it can also conceal bad input. If imported year, month, and day fields are supposed to be clean, use validation or review the source data rather than assuming DATE will reject every unusual component.

Converting date-looking text with DATEVALUE

If a cell contains a date stored as text, DATEVALUE can convert text that Excel recognizes into a date serial. Suppose A2 contains the text March 15, 2025 and the workbook’s regional settings recognize that wording. In that layout, =DATEVALUE(A2) returns the underlying date value; apply a date number format to the formula cell to display it as a readable date.

DATEVALUE depends on how Excel interprets the text, which can depend on regional settings. Text such as 03/04/2025 is ambiguous: one locale may read it as March 4, while another may read it as April 3. If a source file uses a known order, do not assume a slash-separated string will be interpreted correctly on every computer. Inspect sample rows and confirm the converted results before relying on them.

If DATEVALUE returns an error, the text may include extra spaces, an unrecognized month name, or a date order Excel cannot parse under the current locale. Cleaning the source or rebuilding the value from separate year, month, and day fields may be more reliable. Keep the original imported text until you have checked the conversion, especially when the workbook is used for reporting or recordkeeping.

Dates, times, and calculations

Dates and times share Excel’s serial-number model. The whole-number part represents the date, while a fractional part represents a time within the day. As a result, a cell containing both can be displayed with a format that includes date and time, or with a date-only format that hides the time portion. Hidden time values can explain why two cells look like the same date but do not compare as equal.

Date arithmetic usually works best on genuine date values, not TEXT results. Suppose A2 contains March 1, 2025 and B2 contains March 8, 2025. In that layout, =B2-A2 returns 7, the number of elapsed days between the dates. The result is a count, so format that result as General or Number rather than as a date if you want to see 7 instead of a calendar display.

A custom display format can hide details without removing them. If a cell contains a time as well as a date but displays only the date, calculations can still include the time fraction. When elapsed hours, deadlines, or exact comparisons matter, check the underlying values and choose a format that reveals the relevant time information.

Common problems and how to diagnose them

  • A serial number appears: Select the cell and apply a date format. If the display does not change as expected, check whether the entry is text rather than a numeric date value.
  • Changing the format has no effect: The contents may be text. Try converting a recognized date string with DATEVALUE, and verify that Excel parses it as intended.
  • The month and day seem reversed: Check the source’s date order and regional settings. A display-format change cannot repair a text value that was interpreted as the wrong date.
  • A formula result will not sort chronologically: If the formula uses TEXT, it returns text. Keep a real date in a separate column for sorting and calculations, and use the text version only where a label is needed.
  • The formula displays a number: A formula that returns a date may still need a date number format. Formatting controls the appearance of the result cell.
  • Month or minute appears unexpectedly: Check whether the format uses m near time tokens. Excel can interpret it as minutes in a time context.

Choosing a format for sharing and reporting

For worksheets read by people in one region, a familiar local date style can be easiest to understand. For cross-region sharing, an unambiguous style with a written month, such as 15 Mar 2025, avoids the ambiguity of 03/04/2025. A year-first display such as 2025-03-15 is also easy to scan and sort when the values are genuine dates formatted consistently.

Be clear about whether a recipient needs a date value or a string. A formatted date cell can remain a date when copied within a workbook, while a TEXT formula produces characters. Some exports and downstream systems require a particular textual representation; in that case, create the requested text deliberately and preserve the original date if future calculations are needed.

Formatting can also differ across devices because regional settings affect built-in formats and the interpretation of date text. For a report that must look consistent, choose an explicit format and test the workbook in the environment where it will be used. Do not rely on an ambiguous short-date string as the only indication of which day and month were intended.

Frequently Asked Questions

What is the Excel formula to change a date format?
Use TEXT when you need a formatted text result. With a date in A2, =TEXT(A2,"mm/dd/yyyy") returns text in that pattern. To change only how a date cell looks, use Format Cells or the Home tab’s number-format controls instead.

How do I show the month name in an Excel date?
Apply a custom date format such as mmmm d, yyyy to the cell. If you need the result as text, with a date in A2 use =TEXT(A2,"mmmm d, yyyy").

Why does my Excel date formula show a number?
Excel may be displaying the date serial because the result cell is formatted as General or Number. Apply a date format. If the formula returns text, check that it references a real date and that its format code is valid.

Does TEXT keep the result as a date?
No. TEXT returns text, even if its characters look like a date. Use a cell number format when the value needs to stay a date for sorting, comparisons, or calculations.

How do I convert a text date into a real Excel date?
If Excel recognizes the text under the workbook’s regional settings, use DATEVALUE. For example, when A2 contains recognizable date text, =DATEVALUE(A2) returns a date value that you can format. Confirm ambiguous day/month entries before using the converted data.

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.