Adding zero to a date-looking text value can make Excel convert it into a date serial because +0 forces arithmetic. If Excel recognizes the text as a date, the formula returns its numeric value; the zero does not change that value. Format the result as a date to display it as a calendar date. For a Microsoft-documented conversion method, use DATEVALUE and then apply a date format.
What an Excel date serial is
Excel stores dates as sequential numbers so they can be used in calculations. In the default 1900 date system, January 1, 1900 is serial 1; Microsoft’s example gives January 1, 2008 as serial 39448. These are explanatory serial values, not statistics. Microsoft explains the date serial system.
A cell’s number format controls how a value appears, not what value it contains. A serial shown as 39448 can display as a readable date after you apply a date format. Conversely, text that looks like a date may not be a date value Excel can calculate with.
Why =A1+0 converts some text dates
When A1 contains text that Excel recognizes as a date under the current regional settings, =A1+0 asks Excel to perform arithmetic. Excel coerces the recognized text into its numeric date serial; adding zero leaves the serial unchanged. This is a quick coercion shortcut, not a way to repair arbitrary or unrecognized text. Exceljet describes the add-zero shortcut. Microsoft documents the serial model and text-date conversion methods, but does not present +0 as its preferred conversion procedure.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Convert text dates in a worksheet
Quick shortcut for text Excel already recognizes
- In a new cell, enter
=A1+0, replacing A1 with the cell containing the text date. - Format the result cell as Short Date or another appropriate date format. If it displays a serial number first, the conversion may have worked and only the display format needs changing.
- Check that the displayed date is the intended date before filling the formula down or replacing source data.
Microsoft-documented method with DATEVALUE
- In a cell set to General, enter
=DATEVALUE(A1). - Format the result as a date.
DATEVALUEreturns a serial number, so a General-formatted result may look like an ordinary number. - Verify the converted dates. If you need to replace the original text, copy the verified results and use Paste Special as Values, then apply a date format.
Microsoft’s text-date conversion guidance covers the DATEVALUE route and error-checking options. Depending on the detected input and whether error checking is enabled, Excel may also offer conversion for certain text dates with two-digit years.
Choose a conversion method
| Method | Best suited to | Important limits |
|---|---|---|
=A1+0 |
A quick conversion when Excel already recognizes the text as a date | Depends on regional parsing; it is a shortcut, not Microsoft’s documented preferred workflow. Exceljet |
=DATEVALUE(A1) |
An explicit, documented conversion of recognized date text to a serial | Format the result as a date. An omitted year uses the computer’s current year, and time text is ignored. Microsoft |
| Error-checking conversion | Certain detected text dates, including some with two-digit years | Available only when error checking is enabled and Excel flags the particular input. Microsoft |
| Import or source-data cleanup | Repeated or structured imports that need explicit parsing rules | Steps depend on the source format and Excel version; verify results before replacing source data. |
Why a conversion gives an error or the wrong date
The text is not recognized
Adding zero cannot fix text Excel cannot parse. DATEVALUE can return #VALUE! when it cannot recognize the text or the value is outside its documented range. Check separators, extra characters, and the source format rather than repeating the same formula. The Microsoft VALUE function guidance likewise describes conversion of recognizable text, not arbitrary strings.
Month and day order is ambiguous
A value such as 1/2/2024 can mean January 2 or February 1, depending on the date convention Excel uses. Confirm the source’s month/day order and the computer’s regional interpretation before converting a batch. Where possible, use an unambiguous source format and four-digit years. Microsoft’s DATEVALUE documentation describes how recognized date formats affect interpretation.
The year is missing or abbreviated
If a text date omits its year, DATEVALUE uses the computer’s current year. Use four-digit years when possible, and verify how abbreviated years are interpreted in your configuration. Microsoft’s date-system and year-interpretation guidance explains related settings.
A number appears after conversion
This may mean conversion succeeded: the value is now a serial, but its format is General or Number. Apply a date number format rather than changing the formula.
A time is present in the text
DATEVALUE ignores time information in its text argument. If the time must be retained, use a conversion appropriate to the source format and check the result; do not assume DATEVALUE preserves it.
Rank #4
The same date has different serials in two workbooks
Excel workbooks can use either the 1900 or 1904 date system, so the same calendar date can have a different serial in each. Check both workbooks’ date-system settings before treating a serial difference as corruption. Microsoft documents the date-system setting.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Check whether a cell contains text or a date value
Microsoft notes that text dates are left-aligned by default, while numeric values are typically right-aligned. Treat alignment only as a clue because cell alignment can be changed manually. Test with a conversion formula, then confirm that the resulting date is correct. With error checking enabled, Excel may also mark certain text dates and offer conversion choices. Microsoft’s guidance on text dates describes those options.
Free tools Windows power users keep installed
One-click scans. No signup required.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
