You try to subtract one date from another in Excel but get a #NUM! error instead of the expected number of days. This error occurs when Excel cannot perform the calculation due to an invalid date format or a calculation that results in a negative date. This article explains why the #NUM! error appears during date subtraction and provides the correct methods to calculate date differences.
Key Takeaways: Fixing the #NUM! Error in Date Calculations
- Check cell formatting for dates: Ensure both cells are formatted as a valid Excel date, not as text or a general number.
- Use the DATEDIF function: Calculate the difference between two dates in days, months, or years without direct subtraction.
- Apply the DATEVALUE function: Convert text that looks like a date into a proper serial number that Excel can calculate.
Why Excel Shows #NUM! When Subtracting Dates
Excel stores dates as sequential serial numbers, where January 1, 1900, is number 1. When you subtract one date from another, Excel calculates the difference between these two numbers. The #NUM! error appears when one of the values in the calculation is not a valid date number. The most common cause is a cell containing text that looks like a date, such as “2024-13-01” for a non-existent month. Excel cannot convert this text into a serial number, so the subtraction fails.
Another frequent cause is attempting to subtract a later date from an earlier date using certain functions. For example, the legacy DATEDIF function returns #NUM! if the start date is after the end date. The error can also appear if a cell is formatted as text, preventing Excel from recognizing a correctly typed date. Understanding these root causes is the first step to fixing the calculation.
Steps to Correctly Calculate Date Differences
Follow these methods to resolve the #NUM! error and get an accurate result for the number of days, months, or years between two dates.
Method 1: Verify and Correct Date Formats
- Select the date cells
Click on the cell you believe contains the start date. Then look at the Number Format box in the Home tab on the ribbon. - Check the cell format
The format should be a date type like *3/14/2012, 14-Mar, or Long Date. If it says “Text” or “General,” the entry may not be a true date. - Re-enter the date or change the format
If the cell is formatted as Text, change it to a Date format first. Then re-type the date or press F2 and Enter to convert it. - Use the DATEVALUE function for text dates
If your date is stored as text like “March 14, 2024,” use =DATEVALUE(“March 14, 2024”) in a new cell to convert it to a serial number you can use in calculations.
Method 2: Use the DATEDIF Function
- Understand the DATEDIF syntax
The formula is =DATEDIF(start_date, end_date, “unit”). The unit is a code in quotes that defines the return type, like “D” for days. - Enter the formula for days
In a new cell, type =DATEDIF(A2, B2, “D”) where A2 is the start date and B2 is the end date. This returns the number of complete days between them. - Use other interval codes
Replace “D” with “M” for complete months or “Y” for complete years. Use “MD” to find days ignoring months and years, but note this argument can sometimes give unexpected results. - Ensure the start date is earlier
The DATEDIF function will return a #NUM! error if the start_date is later than the end_date. Swap the cell references if needed.
Method 3: Simple Subtraction with DATE Function
- Build valid dates with the DATE function
To avoid typing errors, construct dates using =DATE(year, month, day). For example, =DATE(2024, 3, 14) always creates a valid date. - Subtract the DATE function results
In a cell, enter a formula like =DATE(2024,12,31) – DATE(2024,1,1). This directly subtracts the serial numbers and displays the difference in days. - Reference cells containing DATE functions
Place =DATE(2024,1,1) in cell C2 and =DATE(2024,12,31) in cell D2. Then in E2, use the formula =D2 – C2 to calculate the difference.
If You Still Get a #NUM! Error After Formatting
Sometimes the #NUM! error persists even after checking cell formats. These specific scenarios require targeted fixes.
Cell Contains a Date Outside Excel’s Valid Range
Excel for Windows supports dates from January 1, 1900, to December 31, 9999. If your date is earlier than 1900, it may be stored as text. You cannot calculate differences with it using standard subtraction. A workaround is to keep both dates as text and use a formula to parse the year, month, and day, but this is complex. For historical data, consider calculating differences manually or using specialized software.
Using DATEDIF with the “MD” or “YM” Unit
The DATEDIF arguments “MD” (days ignoring months/years) and “YM” (months ignoring days/years) are known to sometimes return incorrect results or a #NUM! error with certain date combinations, like the end-of-month dates. Microsoft notes these arguments may provide unexpected values. It is best to avoid “MD” and “YM.” Calculate the total days and then derive the remaining months and days using other formulas.
Circular Reference or Volatile Function Interference
A less common cause is if your date subtraction formula indirectly refers to its own cell, creating a circular reference. Excel might show a #NUM! error. Check for circular references via Formulas > Error Checking > Circular References. Also, some volatile functions like TODAY() or NOW() update constantly. If your formula is =TODAY() – A1 and cell A1 somehow depends on the formula’s result, it can cause an error.
Date Subtraction Methods: Key Differences
| Item | Simple Subtraction (A2-B2) | DATEDIF Function |
|---|---|---|
| Primary Use | Calculating difference in days | Calculating difference in days, months, or years |
| Result with Invalid Date | #VALUE! or #NUM! error | #NUM! error |
| Handles Negative Time Span | Returns a negative number | Returns #NUM! error |
| Ease of Use | Simple, direct formula | Requires specific unit argument |
| Function Availability | Basic arithmetic | Legacy function, not in Function Wizard |
You can now identify why a #NUM! error appears when subtracting dates and apply the correct fix. Use cell formatting checks and the DATEVALUE function to ensure Excel recognizes your data as true dates. For reliable calculations across different units, the DATEDIF function is the standard tool, though simple subtraction works perfectly for finding the number of days between two valid dates. For advanced date analysis, explore the NETWORKDAYS function to calculate working days excluding weekends.