Excel Date Serial Numbers: Why Adding Zero Converts Text to Dates

Adding zero to a cell containing a date as text forces Excel to evaluate the text as a number, and because Excel stores dates as serial numbers, the result is a real date value. This works because the zero triggers Excel’s implicit conversion of text that looks like a date, and the arithmetic operation returns the underlying serial number which is then formatted as a date.

Formula

The formula for converting text to a date by adding zero is:

Serial Number = DATEVALUE(text_date)

Where:

• text_date is a string that Excel recognizes as a date (e.g., “2025-01-15”, “15-Jan-2025”).
• Serial Number is the number of days since January 0, 1900 (for Windows Excel) or January 1, 1904 (for Mac Excel with the 1904 date system).

Adding zero to a text date is equivalent to using the DATEVALUE function. The arithmetic operation forces Excel to convert the text to a number, which is the serial date.

Worked Example

Using the default input from the calculator above:

1. Text date: “2025-01-15”
2. Add zero: 1 (yes)
3. Serial number = DATEVALUE(“2025-01-15”) = 45672 (since 2025-01-15 is 45,672 days after January 0, 1900)
4. Formatted date: 45672 displayed as a date gives 2025-01-15.

If you entered “2025-01-15” in a cell and added zero (e.g., =A1+0), Excel would return the serial number 45672, and if the cell is formatted as a date, it shows 2025-01-15.

Why Excel Stores Dates as Serial Numbers

Excel stores dates as sequential serial numbers so they can be used in calculations. January 1, 1900 is serial number 1, and each day after increments by one. For example, January 1, 2025 is serial number 45672. This system allows you to subtract dates to find the number of days between them, add days to a date, or use dates in formulas. The 1900 date system is the default for Windows Excel, while Mac Excel historically used a 1904 date system (where January 1, 1904 is serial 0). This difference can cause date serial numbers to differ by 1,462 days between the two systems.

How Adding Zero Converts Text to Dates

When you type a date as text (e.g., “2025-01-15”), Excel may not recognize it as a date if the cell is formatted as text or if the entry is not in a standard date format. Adding zero to that cell (e.g., =A1+0) forces Excel to parse the text as a number. Since the text looks like a date, Excel interprets it as a date serial number. The zero is added to the serial number, but since adding zero doesn’t change the value, the result is the serial number itself. If the cell is formatted as a date, you see the date; if formatted as a number, you see the serial number.

This trick is often used to convert text dates imported from other systems into real date values that Excel can sort, filter, and use in calculations.

When to Use This Method vs. Other Methods

Adding zero is a quick way to convert text dates, but it only works if the text is in a recognizable date format. Alternative methods include:

• DATEVALUE function: Converts a text date to a serial number, but returns #VALUE! if the text is not a valid date.
• Text to Columns wizard: In the Data tab, you can split text and choose a date format for conversion.
• Paste Special with Add: Copy a cell containing 0, select the text dates, and use Paste Special > Add to convert them.
• Error checking: If Excel shows a green triangle, you can click it and select “Convert to Number”.

Adding zero is best for single cells or small ranges where the text is already in a standard date format like YYYY-MM-DD or MM/DD/YYYY. For inconsistent formats, the Text to Columns wizard is more robust.

Common Problems and Misconceptions

One common problem is that adding zero to a text date that is not recognized as a date returns #VALUE! instead of a serial number. For example, “January 15, 2025” might not convert if the system locale uses a different date order. Another issue is that after adding zero, the cell may display a number like 45672 instead of a date. This happens if the cell format is General or Number. To fix it, apply a date format to the cell.

A common misconception is that adding zero converts text to a date in the same way as changing the cell format. Changing the format only changes the display, not the underlying value. If the cell contains text, changing the format to Date will not convert it; you still need to perform an operation like adding zero to trigger the conversion.

Frequently Asked Questions

Why does adding zero change the appearance of my date text?

Adding zero performs a mathematical operation that forces Excel to interpret the text as a number. Since the text is a date, Excel converts it to its serial number. The cell then displays the serial number according to the cell’s number format. If the format is General, you’ll see the serial number; if you change it to a Date format, you’ll see the date.

What if adding zero gives me #VALUE!?

This means Excel cannot recognize the text as a date. Check the format of the text. It must be in a date format that Excel understands, such as YYYY-MM-DD, MM/DD/YYYY, or DD-MMM-YYYY, depending on your system’s locale. If the text includes extra spaces or non-date characters, remove them first.

Can I use this method for a column of dates?

Yes, but you need to apply it to each cell. You can enter a formula like =A1+0 in a new column and drag it down, or use Paste Special with Add: copy a cell with 0, select the range of text dates, and use Paste Special > Add. This will add zero to each cell and convert them all.

Is there a difference between adding zero and using VALUE function?

Adding zero is essentially the same as using the VALUE function, which converts a text string that looks like a number to a number. Since dates are stored as numbers, VALUE(“2025-01-15”) returns the same serial number as “2025-01-15″+0. Both methods rely on Excel’s ability to parse the text as a date.

Why does my date serial number differ from a colleague’s?

This is likely due to the 1904 date system. If your workbook uses the 1904 date system (common in older Mac Excel files), the serial numbers are offset by 1,462 days. Check File > Options > Advanced > When calculating this workbook, and see if “Use 1904 date system” is checked. If so, dates will have different serial numbers than in the 1900 system.

Can I convert text to date without using a formula?

Yes, you can use the Text to Columns wizard. Select the column, go to Data > Text to Columns, choose Delimited, then in Step 3 select Date and choose the format that matches your text. This converts the text to real dates without needing a formula.

Related

• Most Popular Usernames of All Time
• Japanese School Rivalry Games: What Makes Them Different
• Delete Temporary Files in Windows 11: Step-by-Step

in t | Enter date as text | - | 2025-01-15 | 0
in z | Add zero (1=yes, 0=no) | - | 1 | 0
out d | Resulting serial number | - | DATEVALUE(t) * z | 0
out date | Formatted date | - | d | 0