Excel Error Codes

Fix the #VALUE! Error in Excel DATEDIF for Blank Dates

John Wilson Oct 14, 2026 870 views

Question details

The user needs a solution to resolve the #VALUE! error returned by the DATEDIF formula when calculating durations with a blank or incomplete end date.

How to Fix the #VALUE! Error in the Excel DATEDIF Function

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating the days, months, or years between a start date and an end date, but the period is still ongoing, leaving the end date cell blank.
Observed behavior: The DATEDIF function throws a #VALUE! error because it cannot process a blank cell or improperly handled future date as a valid end date argument.

Before you start

Verify that the cells referenced in your DATEDIF formula are formatted correctly as Dates and that the start date is chronologically before the end date.

Solution 1Recommended

Use the IF and TODAY Functions to Handle Ongoing Dates

This method automatically calculates the date difference up to the current day if the end date cell is left blank.

By nesting an IF statement inside the DATEDIF function, you can instruct the spreadsheet to substitute today's date whenever the end date is missing. This is ideal for tracking ongoing projects or employee tenures.

Select the target cell

Click on the cell where you want the calculated date difference to appear.

Enter the nested formula

Type the formula: =DATEDIF(A2, IF(B2="", TODAY(), B2), "D") (assuming A2 contains your start date and B2 is your end date).

Apply and drag

Press Enter to execute the formula. If you are calculating multiple rows, click the small square at the bottom-right of the cell and drag it down to fill the rest of the column.

Use the IF and TODAY Functions to Handle Ongoing Dates

Pro Tip: Change the "D" argument to "M" for months or "Y" for years depending on the unit of time you wish to measure.

Solution 2

Wrap the Formula in IFERROR to Hide the Error entirely

Use this solution if you prefer the calculation cell to remain completely blank when an end date is missing.

Solution 3

Calculate Days Using Simple Subtraction

An alternative mathematical approach to calculate the number of days without relying on the undocumented DATEDIF function.

WPS Spreadsheet Solution

Seamlessly Calculate Dates and Fix Formula Errors with WPS Office

Handle complex date tracking and avoid frustrating formula errors with WPS Spreadsheet. It provides complete support for all standard and advanced date functions like DATEDIF, IF, IFERROR, and TODAY, enabling you to manage timelines effortlessly.

1. Download and open WPS Office: Launch WPS Office on your device and select 'Spreadsheet' from the main menu.
2. Open your data file: Import your existing Excel workbook that contains the date tracking errors.
3. Apply the fix: Use the provided nested IF or IFERROR formulas exactly as you would in Microsoft Excel.
4. Save and share: Save your document natively as an .xlsx file, ensuring anyone using Microsoft Office can view your corrected formulas flawlessly.

100% compatible with Microsoft Excel (.xlsx and .xls) formats and formulasFlawless execution of advanced nested functions like DATEDIF and IFERRORLightweight, fast, and completely free to use across Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why does my DATEDIF formula return a #NUM! error instead of #VALUE!?

A #NUM! error occurs in the DATEDIF function when the start date is later than the end date. Ensure your chronological order is correct, or wrap your formula in an IF statement to check date order before calculating.

Can I use the DATEDIF function in WPS Spreadsheet?

Yes, WPS Spreadsheet fully supports the DATEDIF function and all its arguments (Y, M, D, MD, YM, YD), maintaining total compatibility with Microsoft Excel.

How do I fix a #VALUE! error if my dates are formatted as text?

If your dates are accidentally stored as text strings, DATEDIF cannot process them. Select the column with your dates, go to the 'Data' tab, click 'Text to Columns', and click 'Finish' to instantly convert the text into valid date formats.

Microsoft Office Error Codes