How to Calculate Date Differences in Excel Without Blank-Cell Errors
Question details
The user wants to calculate the difference between two dates without generating errors when one of the date cells is blank or contains an invalid date.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the days between two dates in a spreadsheet where some date entries might be incomplete, missing, or invalid, and needing to prevent formula errors that could affect downstream calculations like AVERAGEIF.
- Observed behavior
- Standard date-difference formulas return errors, zeroes, or incorrect values when referencing blank or invalid cells, which interferes with other formulas.
Ensure that the cells containing your dates are formatted as 'Date' rather than 'Text'. Verify that your dataset does not contain fundamentally invalid dates, such as 0-Jan-1900, which cannot be processed by standard date functions.
Use IF and DATEDIF to Return a Blank Cell
Wrap your date calculation in an IF and OR statement to output a blank string when either the start or end date is missing.
This method is highly recommended if you plan to use an AVERAGEIF formula later, as blank cells are automatically ignored and will not skew your average downward.
Click on the cell where you want the calculated date difference to appear.
Type `=IF(OR(K7="",M7=""),"",DATEDIF(K7,M7,"d"))` into the formula bar. Replace K7 with your start date cell and M7 with your end date cell.
Press Enter to apply the formula, then drag the fill handle at the bottom-right corner of the cell down to apply it to the rest of your column.

Use IF and DAYS to Return Zero
If your data model prefers a numerical '0' instead of a blank cell for missing dates, use the DAYS function combined with an IF statement.
Calculate Dates Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date formulas like DATEDIF, DAYS, and conditional IF statements. It provides a seamless experience for handling large datasets, filtering errors, and calculating date differences perfectly.
- 1. Open your file in WPS: Launch WPS Office and open your spreadsheet file containing the dates.
- 2. Select the formula cell: Click on the empty cell where the date difference result should be displayed.
- 3. Insert the conditional formula: Input the `=IF(OR(start="",end=""),"",DATEDIF(start,end,"d"))` formula and press Enter.
- 4. Use AutoFill for the column: Hover over the bottom-right corner of the cell until the cross cursor appears, then double-click or drag downwards to fill the formula across your dataset.

Frequently Asked Questions
Why does my DATEDIF formula return a #NUM! error?
A #NUM! error in DATEDIF usually occurs if the start date is chronologically later than the end date. Wrapping the formula in an IF statement or using the ABS function can help manage negative date differences.
What is the difference between DATEDIF and DAYS?
DATEDIF allows you to calculate the difference in various units, such as days ("d"), months ("m"), or years ("y"). The DAYS function strictly calculates the difference in days between two dates and requires the end date to be listed first.
How do I ignore blank cells when averaging the date differences?
If you use the recommended formula to return a blank string ("") instead of an error or zero, functions like AVERAGE or AVERAGEIF will automatically ignore those cells, preventing missing data from artificially lowering your average.
Why does Excel consider 0-Jan-1900 an invalid date?
The standard date system in modern spreadsheet software starts on January 1, 1900 (represented by serial number 1). Because 0-Jan-1900 falls outside this supported historical range, it is treated as text or an invalid entry and cannot be calculated using standard date functions.




