How to calculate days between dates for a whole column in Google Sheets
Google Sheets formula
=ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8))Use one ARRAYFORMULA at the top: =ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8)).
- Result on the example
- a 7-row list that spills from D2 in
D2 - Excel
- See below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
ARRAYFORMULA makes one formula work on whole ranges: C2:C8-B2:B8 subtracts row by row and fills the column. The IF leaves rows without an end date empty, so unfinished tasks don't show negative numbers.
=ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8))Result{7 rows × 1 col} 25, 18, "", 42, …
C2:C87 rows × 1 col: 2/27/2026, 3/20/2026, (empty), 5/13/2026, …A range: 7 cells in one column.
C2:C8=""{7 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
B2:B87 rows × 1 col: 2/2/2026, 3/2/2026, 3/23/2026, 4/1/2026, …A range: 7 cells in one column.
C2:C8-B2:B8{7 rows × 1 col} 25, 18, -46104, 42, …The - operator subtracts.
IF(C2:C8="","",C2:C8-B2:B8){7 rows × 1 col} 25, 18, "", 42, …IF: Returns one value if a test is true, another if false.- logical_test
C2:C8=""{7 rows × 1 col} FALSE, FALSE, TRUE, FALSE, … - value_if_true
"" - value_if_false
C2:C8-B2:B8{7 rows × 1 col} 25, 18, -46104, 42, …
- logical_test
ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8)){7 rows × 1 col} 25, 18, "", 42, …ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.- array_formula
IF(C2:C8="","",C2:C8-B2:B8){7 rows × 1 col} 25, 18, "", 42, …
- array_formula
In Excel
=IF(C2:C8="","",C2:C8-B2:B8).Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =ARRAYFORMULA(IF(C2:C8="","",DAYS(C2:C8,B2:B8))) | spills 7 × 1 | The same with DAYS, which takes the end date first. |
Common mistakes
- Clear the cells below D2 first. ARRAYFORMULA can't overwrite typed values and shows #REF! instead.
- Open-ended ranges like B2:B are handy but slow on very large tabs. Keep the IF so empty rows return "".
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 for a whole column
D2, write one formula that returns the days from start to end for every task, leaving rows without an end date empty.Questions
How do I count working days for the whole column?
NETWORKDAYS doesn't work row by row inside ARRAYFORMULA. Use MAP: =MAP(B2:B8,C2:C8,LAMBDA(s,e,IF(e="","",NETWORKDAYS(s,e)))).
How do I put the header in the same formula?
In D1: ={"Days";ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8))}.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.