Excel Convert Date to Text or Number?
Excel stores dates as serial numbers, even when a cell displays a calendar date. To turn a date into readable text, use TEXT or change the cell’s number format; to retrieve its underlying number, change the format to General or use a formula such as =A1*1.
How Excel Stores and Displays Dates
An Excel date is a number that counts days from a starting point. In the standard 1900 date system used by most Windows workbooks, January 1, 1900 corresponds to serial number 1. A date such as July 1, 2024 is stored as a larger serial value. Excel displays that value as a date when the cell has a date format.
This distinction between a value and its display explains why changing a date’s appearance does not usually change the underlying data. A cell can contain a numeric date serial and show it as “Jul 1, 2024,” “7/1/24,” or “Monday, July 1.” Formatting controls the presentation; formulas and calculations generally use the stored value.
Excel also supports a 1904 date system, historically used by some Mac workbooks. The same calendar date has a different serial number under that system. A workbook’s date system is set in its options, so serial values copied between workbooks using different systems can be offset. For ordinary formatting and conversion within one workbook, Excel handles the selected system automatically.
Convert a Date to Text with TEXT
Use TEXT when a formula needs to return a date as text in a specific layout. Its syntax is =TEXT(value, format_text). For example, if A1 contains a date, =TEXT(A1,"yyyy-mm-dd") returns text such as 2024-07-01.
=TEXT(A1,"mmmm d, yyyy")returns a full month name, day, and year, such as “July 1, 2024.”=TEXT(A1,"m/d/yy")returns a compact display such as “7/1/24.”=TEXT(A1,"ddd, mmm d")returns an abbreviated weekday and month, such as “Mon, Jul 1.”
The format codes determine what appears in the result. In a date format, m represents a month, while d represents a day and y a year. Repeated letters generally request more detail: mm gives a two-digit month, mmmm spells out the month, and yyyy displays a four-digit year. Excel’s date and time format conventions can vary in how codes are interpreted depending on their context, so use a clear date pattern and check the displayed result.
TEXT returns a text string, not another date value. That is useful for labels, reports, and combining dates with words—for example, ="Due: "&TEXT(A1,"mmm d, yyyy"). It is less suitable when the result must remain a date for sorting, date arithmetic, or date-based filtering. Keep the original date in a separate cell if you need both a calculation-ready value and a text version.
Show the Underlying Date Number
To reveal the serial number without changing the date value, select the cell and choose Home > Number Format > General. You can also open Format Cells and select General. The date will display as a number; selecting a different date format later will display the same underlying value as a date again.
To create a separate numeric result with a formula, use =A1*1 or =VALUE(A1) when A1 contains a date Excel recognizes as a date. The result is the serial value. In some cases, simply referring to the date in a formula keeps the value numeric; the important point is that the referenced cell must contain a real Excel date, not date-like text.
For instance, if A1 contains July 1, 2024, changing its format to General exposes its serial number. The exact displayed serial depends on the workbook’s date system. If you instead see the original date characters unchanged, the cell may contain text rather than a date serial.
Choose the Right Method
- Use cell formatting when you only want a different visible date style and need to preserve a usable date value. Choose a date format or create a custom one.
- Use TEXT when a formula must produce a particular text string, such as a date embedded in a sentence or a consistently formatted export field.
- Use General or a numeric formula when you need to inspect or expose the date serial for a calculation or troubleshooting.
- Use DATE when you need to build a genuine date from separate year, month, and day values:
=DATE(2024,7,1). This avoids relying on ambiguous typed date text.
Changing a cell’s number format is not the same as converting its contents to text. A date displayed as 2024-07-01 can still be numeric. Conversely, text that looks exactly like a date may not support subtraction or chronological sorting as expected.
Common Problems and Fixes
The date displays as a number. The cell is probably set to General or a numeric format. Apply a date format to display it as a calendar date; the number itself may be a valid date serial.
The formula returns a number instead of a formatted date. If you want a date value, format the formula cell as a date. If you need the formula to return text in a chosen pattern, wrap the value in TEXT.
The formula returns the date unchanged or produces an error. Check whether the source is stored as text. A value imported from a file or entered in a format Excel does not recognize may need to be converted. Depending on the data, options include Text to Columns, correcting the source format, or rebuilding the value with DATE and components extracted from the text. Be cautious with dates such as 03/04/2024: they can mean March 4 or April 3 depending on regional conventions.
Months appear where minutes were expected. In Excel date/time format codes, m can mean month or minute depending on context. When formatting times, minutes are typically written in a time context such as h:mm. Check the result rather than assuming every m means the same thing.
Sorting or date arithmetic behaves strangely. Some entries may be text while others are real dates. Convert the text entries consistently, then check that the cells sort chronologically and that subtracting two dates returns the expected number of days.
Frequently Asked Questions
Does Excel store dates as text or numbers?
Excel normally stores recognized dates as numeric serial values. A date can be stored as text if it was imported or entered in a form Excel did not interpret as a date.
How do I convert a date to text in Excel?
Use =TEXT(A1,"yyyy-mm-dd"), replacing A1 and the format pattern as needed. The formula result is text, so it is not automatically suitable for date calculations.
How do I convert a date to a number in Excel?
Change the cell’s number format to General to display its serial value. For a separate formula result, use a numeric reference such as =A1*1, provided A1 contains a recognized date.
Why does Excel show a date as a five-digit number?
That is usually the date’s serial value shown with General or another numeric format. Apply a date format to display it as a calendar date again.
Will TEXT keep my date usable in calculations?
No. TEXT returns text. Keep the original date value for calculations, or convert the text back to a recognized date before using date arithmetic.
Why did a date change when I opened or copied a workbook?
Different regional interpretations can swap the month and day in ambiguous text, and workbooks can use different date systems. Check the source format and the workbook’s date-system setting when serial values differ unexpectedly.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.