Formula Errors

Fix Excel #NUM Error in DATEDIF for Future Project Dates

Anum Mushtaq Oct 21, 2026 868 views

Question details

The user needs to calculate elapsed project duration using the DATEDIF function but encounters a #NUM! error when the project start date is in the future.

Fix Excel #NUM! Error When a Project Start Date Is in the Future

Product: Excel
Device & OS: not provided
Scenario: Tracking project progress percentages where some tasks have start dates that are later than today.
Observed behavior: The DATEDIF function returns a #NUM! error because it cannot process a start date that is greater than the end date (today).

Before you start

Ensure your project start and end date cells are correctly formatted as Dates, and note their exact cell references before applying the modified formula.

Solution 1Recommended

Use the IF Function to Handle Future Start Dates

Wrap your DATEDIF calculation in an IF function to return 0% if the start date is in the future, avoiding the #NUM! error.

The DATEDIF function requires the first date argument to be earlier than or equal to the second date argument. When using TODAY() as the second argument, future start dates violate this rule, causing a #NUM! error.

By adding an IF statement, you can check if the start date is greater than today. If it is, the formula forces a 0% return value before attempting the DATEDIF calculation.

Select the target cell

Click on the cell where you want to calculate the project progress percentage.

Enter the conditional formula

Type the following formula: =IF(E6>TODAY(),0,MIN((DATEDIF(E6,TODAY(),"d")+1)/(DATEDIF(E6,F6,"d")+1),100%)) (assuming E6 is the start date and F6 is the end date).

Apply percentage formatting

Navigate to the Home tab and click the Percentage style icon to format the decimal result as a proper percentage.

Use the IF Function to Handle Future Start Dates

Formula Breakdown: The MIN function ensures that the calculated progress never exceeds 100%, which is highly useful for projects that extend past their original planned end date.

Advanced Spreadsheet Tool

Easily Fix Formula Errors and Calculate Project Durations with WPS Spreadsheet

WPS Spreadsheet is fully equipped to handle DATEDIF, IF, and MIN functions. You can seamlessly calculate project progress, handle complex date logic, and format data without encountering compatibility issues.

1. Open your project tracker: Launch WPS Spreadsheet and open the workbook containing your project dates.
2. Input the error-handling formula: Select the progress cell and paste the modified formula: =IF(E6>TODAY(),0,MIN((DATEDIF(E6,TODAY(),"d")+1)/(DATEDIF(E6,F6,"d")+1),100%)).
3. Format as a percentage: Go to the Home tab and click the '%' icon to display the result as a proper progress percentage.

100% compatible with Microsoft Excel formulas and date functionsBuilt-in error checking to help troubleshoot #NUM! and #VALUE! errors easilyLightweight, fast, and completely free for everyday data analysis

Frequently Asked Questions

Why does the DATEDIF function return a #NUM! error?

The DATEDIF function calculates the difference between two dates. It returns a #NUM! error if the start date is strictly greater than the end date. In project tracking, if your start date is in the future and you are comparing it to TODAY(), the formula fails.

How can I cap my project completion percentage at 100%?

You can wrap your duration calculation in the MIN function, such as =MIN(your_formula, 100%) or =MIN(your_formula, 1). This forces the cell to display 100% even if the elapsed time exceeds the planned project duration.

Are the DATEDIF and IF functions compatible across different spreadsheet software?

Yes, core functions like DATEDIF, IF, MIN, and TODAY() are standard and work identically across major spreadsheet programs, including Microsoft Excel and WPS Spreadsheet.

Excel & Data Problems