# How to calculate days between dates for a whole column in Google Sheets

Use one ARRAYFORMULA at the top: `=ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8))`.

**Google Sheets:** `=ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8))`

**Excel:** In Excel 365 a range formula spills by itself: `=IF(C2:C8="","",C2:C8-B2:B8)`.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Task | Start | End | Days |
| Design | 2026-02-02 | 2026-02-27 |  |
| Build | 2026-03-02 | 2026-03-20 |  |
| Test | 2026-03-23 |  |  |
| Launch | 2026-04-01 | 2026-05-13 |  |
| Review | 2026-05-18 | 2026-05-22 |  |
| Retro | 2026-06-01 | 2026-06-01 |  |
| Plan | 2026-06-08 |  |  |

Formula in D2: `=ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8))`

Result (spills 7 × 1): 25; 18; ; 42; 4; 0; 

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

## Other ways

- `=ARRAYFORMULA(IF(C2:C8="","",DAYS(C2:C8,B2:B8)))`: 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 "".

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

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-for-a-whole-column-in-google-sheets