Calculation Issues

Calculate Days Between Two Dates Only When Both Exist in Excel

Kushani Nimanthika Oct 19, 2026 868 views

Question details

The user needs to calculate the difference in days between two date columns (e.g., admission and discharge dates), but wants the result to remain blank if either of the dates is missing to prevent calculation errors when copying the formula down a dataset.

How to Calculate Days Between Two Dates Only When Both Exist in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating the duration or interval between two specific dates across multiple records where some data entries might be incomplete.
Observed behavior: The formula successfully returns the exact number of days when both start and end dates exist, and outputs a blank cell if one or both dates are empty.

Before you start

Verify that your data columns are correctly formatted as Dates, and identify the exact cell references for your start date (e.g., A2) and end date (e.g., B2) before applying the formula.

Solution 1Recommended

Use the IF and OR formula for conditional date calculation

This is the most effective method to ensure your date subtraction only triggers when both the start and end date cells contain data.

By nesting an OR function inside an IF statement, Excel checks if either the start date or the end date cell is blank. If true, it returns a blank string; if false (both exist), it subtracts the start date from the end date.

Select the destination cell

Click on the cell where you want the calculated days to appear, for example, C2.

Enter the logical formula

Type the formula =IF(OR(A2="",B2=""),"",B2-A2) assuming A2 is your admission date and B2 is your discharge date. Press Enter.

Copy the formula down the column

Click the bottom-right corner of cell C2 (the fill handle) and drag it down to apply the formula to all relevant rows.

Format as Number

Highlight the result column, go to the Home tab, select 'Number' from the formatting dropdown, and decrease decimals to zero.

Use the IF and OR formula for conditional date calculation

Result Output: Your column will now neatly display the number of days for complete records while keeping incomplete rows perfectly blank.

Solution 2

Fixing negative values or incorrect references

Sometimes users experience strange negative results (like -44995) when the cell references in the formula do not match the actual date columns.

WPS Spreadsheet Solution

Seamlessly Calculate Dates with WPS Spreadsheet

WPS Spreadsheet offers full support for advanced logical functions like IF and OR, allowing you to seamlessly calculate date differences and manage large datasets efficiently.

1. Open data in WPS: Launch WPS Spreadsheet and open your workbook containing the dates.
2. Apply the formula: Select your target cell and input =IF(OR(A2="",B2=""),"",B2-A2).
3. Fill down: Press Enter, then drag the fill handle at the bottom right of the cell downwards to apply it to other rows.
4. Format properly: Right-click the highlighted results, choose 'Format Cells', and set them to 'Number' with 0 decimal places.

Fully compatible with Microsoft Excel formulas and date formatsIntuitive drag-and-fill features for fast data processingLightweight application with robust cell formatting tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date subtraction formula return a #VALUE! error?

This usually happens if one of the referenced cells contains text or spaces instead of a valid date, or if a cell looks blank but actually contains a hidden space character. Ensure both cells are either completely empty or contain properly formatted dates.

Can I calculate only working days between two dates using this method?

Yes, you can replace the simple subtraction part (B2-A2) with the NETWORKDAYS function to exclude weekends. The updated formula would be: =IF(OR(A2="",B2=""),"",NETWORKDAYS(A2, B2)).

What if I want to return a zero instead of a blank when a date is missing?

Simply replace the empty quotation marks in the middle of the IF statement with a zero. The formula will look like this: =IF(OR(A2="",B2=""),0,B2-A2).

How do I conditionally calculate the difference in months instead of days?

You can use the DATEDIF function paired with your IF and OR logic. To calculate complete months only when both dates exist, use the formula: =IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"M")).

Excel & Data Problems