Function Problems

Fix Excel DATEDIF Returning Numbers for Blank Cells

Maira Mehtab Oct 8, 2026 868 views

Question details

The user needs to stop the DATEDIF function from calculating elapsed days when the source date cell is blank, as it returns an erroneous value that skews average calculations.

How to Fix Excel DATEDIF Returning a Number for Blank Cells

Product: Excel
Device & OS: not provided
Scenario: Calculating the days elapsed until TODAY() across a column of dates where some cells are left blank.
Observed behavior: When DATEDIF references a blank cell, Excel treats it as January 0, 1900, returning an unexpectedly large number of days instead of remaining blank.

Before you start

Identify the exact cell reference containing your source dates and decide whether you want empty cells to return a complete blank or a zero in your final column.

Solution 1Recommended

Use an IF Statement with the Not-Equal Operator

Wrap your DATEDIF formula inside an IF statement to verify the source cell is not empty before executing the calculation.

This method is the most efficient way to handle empty cells in Excel formulas. By instructing Excel to only run DATEDIF when the cell does not equal a blank string, you prevent it from treating empty cells as a default zero date.

Select the target output cell

Click on the cell where you want the DATEDIF result to appear.

Input the IF formula

Enter the formula =IF(D5<>"",DATEDIF(D5,TODAY(),"d"),""). Replace 'D5' with the reference to your actual date cell.

Apply to the rest of your data

Press Enter to confirm, then click and drag the fill handle at the bottom right of the cell to copy the formula down your column.

Use an IF Statement with the Not-Equal Operator

Average Calculation Fixed: Because the formula now returns a true empty string ("") instead of a zero or large number, your AVERAGE functions will correctly ignore these rows.

Solution 2

Use the ISBLANK Function

Alternatively, use the ISBLANK function to explicitly check for completely empty cells before calculating.

Calculate Dates Easily with WPS Spreadsheet

Master Complex Date Calculations with WPS Office

WPS Spreadsheet fully supports advanced data functions like DATEDIF, IF, and ISBLANK. You can seamlessly calculate date differences, handle blank cells, and maintain accurate averages using the exact same formulas.

1. Open your file in WPS: Launch WPS Spreadsheet and open the document containing your dates.
2. Enter the formula: Click the target cell and type =IF(D5<>"",DATEDIF(D5,TODAY(),"d"),"").
3. Fill the column: Press Enter, then double-click the small square at the bottom-right of the cell to instantly apply the logic to all rows.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Lightweight application with incredibly fast calculation speeds for large datasets.Intuitive interface that makes building and troubleshooting nested formulas easy.

microsoft office alternative - wps office

Frequently Asked Questions

Why does DATEDIF return a massive number for blank cells?

In spreadsheet programs, a blank cell referenced in a math or date calculation is treated as zero. In date serial numbers, zero corresponds to January 0, 1900. DATEDIF then calculates the difference between 1900 and TODAY(), resulting in tens of thousands of days.

Can I make the formula return a zero instead of a blank?

Yes. If you prefer a numeric zero for blank rows instead of an empty cell, change the last argument of the IF statement to a 0 (without quotes). For example: =IF(D5<>"",DATEDIF(D5,TODAY(),"d"),0).

Does this IF logic work for months and years in DATEDIF?

Yes. The same IF statement structure works perfectly regardless of the time interval. Simply change the last DATEDIF argument from "d" (days) to "m" (months) or "y" (years), such as =IF(D5<>"",DATEDIF(D5,TODAY(),"m"),"").

Excel & Data Problems