# How to calculate the number of overlapping days between two date ranges in Excel

Take the later start and the earlier end: `=MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1)`.

**Excel:** `=MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee | Leave start | Leave end | Days during project |  | Project start | 2026-07-06 |
| Ana | 2026-06-29 | 2026-07-08 |  |  | Project end | 2026-08-14 |
| Ben | 2026-07-20 | 2026-07-24 |  |  |  |  |
| Cara | 2026-08-11 | 2026-08-21 |  |  |  |  |
| Dev | 2026-06-01 | 2026-06-12 |  |  |  |  |
| Eli | 2026-07-01 | 2026-08-31 |  |  |  |  |

Formula in D2, filled to D6: `=MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1)`

Result: 3; 5; 4; 0; 40

## How it works

Two ranges overlap from the later of the two starts to the earlier of the two ends. MAX and MIN find those dates, the difference plus 1 counts the days, and `MAX(0, …)` turns ranges that don't meet, like Dev's June leave, into 0 instead of a negative number.

## Other ways

- `=MAX(0,NETWORKDAYS(MAX(B2,$G$1),MIN(C2,$G$2)))`: Counts the overlapping working days. (Weekdays only.)
- `=IF(AND(B2<=$G$2,C2>=$G$1),MIN(C2,$G$2)-MAX(B2,$G$1)+1,0)`: Tests for an overlap first, which makes the logic easier to read.

## Common mistakes

- Without the +1 a one-day overlap counts as 0.
- Lock the project dates with $ so every row compares with the same range.

## Questions

**How do I just check whether two ranges overlap?**

`=AND(B2=$G$1)`.

**How do I split a range into days per month?**

Use the same MIN and MAX overlap with each month's first and last day.

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/overlapping-days-between-two-date-ranges