Spreadsheet Practice

How to calculate days between dates only when both dates are filled in Excel

Formula (Excel and Google Sheets)

=IF(OR(B2="",C2=""),"",C2-B2)

Return empty text when either date is missing: =IF(OR(B2="",C2=""),"",C2-B2).

Result on the example
one result per row in D2, filled down to D7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D7. Change any input and the result updates, or edit the formula to experiment.

How it works

A blank cell counts as 0, which is day 0 of 1900, so subtracting it gives a meaningless number like -46147. IF checks both cells first and returns empty text when either is blank, so averages and charts skip the row.

=IF(OR(B2="",C2=""),"",C2-B2)

Result3

  1. B25/4/2026

    The value in B2.

  2. B2=""FALSE

    The = operator tests whether the two sides are equal.

  3. C25/7/2026

    The value in C2.

  4. C2=""FALSE

    The = operator tests whether the two sides are equal.

  5. OR(B2="",C2="")FALSE

    OR: TRUE if any argument is true.

    • logical1B2=""FALSE
    • logical2C2=""FALSE
  6. C2-B23

    The - operator subtracts.

  7. IF(OR(B2="",C2=""),"",C2-B2)3

    IF: Returns one value if a test is true, another if false.

    • logical_testOR(B2="",C2="")FALSE
    • value_if_true""not used
    • value_if_falseC2-B23

In Google Sheets

The same formula works in Google Sheets: =IF(OR(B2="",C2=""),"",C2-B2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(COUNT(B2:C2)=2,C2-B2,"")3, , 6…COUNT is 2 only when both cells hold numbers, so it also skips text like "TBD".
=C2-B23, -46,147, 6…Different resultWithout the check, blanks count as 0. Shows large negative numbers for the rows without a ship date.

Common mistakes

  • Return "" (empty text), not 0. A 0 counts in AVERAGE and pulls it down; "" is ignored.
  • A cell with a space looks blank but isn't. The COUNT version handles that too.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate days between dates only when both dates are filled

In D2, calculate the days from ordered to shipped, leaving the cell empty when either date is missing. Fill down to D7.

Questions

How do I average only the filled rows?

AVERAGE skips text, so =AVERAGE(D2:D7) ignores the "" rows.

How do I show "Pending" instead of a blank?

=IF(B2="","",IF(C2="","Pending",C2-B2)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.