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

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

**Excel:** `=IF(OR(B2="",C2=""),"",C2-B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Order | Ordered | Shipped | Days to ship |
| PO-71 | 2026-05-04 | 2026-05-07 |  |
| PO-72 | 2026-05-05 |  |  |
| PO-73 | 2026-05-06 | 2026-05-12 |  |
| PO-74 |  |  |  |
| PO-75 | 2026-05-11 | 2026-05-11 |  |
| PO-76 | 2026-05-12 |  |  |

Formula in D2, filled to D7: `=IF(OR(B2="",C2=""),"",C2-B2)`

Result: 3; ; 6; ; 0; 

## 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.

## Other ways

- `=IF(COUNT(B2:C2)=2,C2-B2,"")`: COUNT is 2 only when both cells hold numbers, so it also skips text like "TBD".
- `=C2-B2`: Without 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/days-between-dates-only-when-both-are-filled