How to calculate the number of overlapping days between two date ranges in Excel
Formula (Excel and Google Sheets)
=MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1)Take the later start and the earlier end: =MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1).
- Result on the example
- one result per row in
D2, filled down toD6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.
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.
=MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1)Result3
C27/8/2026The value in C2.
$G$28/14/2026The value in G2.
MIN(C2,$G$2)46211MIN: Smallest number.- number1
C27/8/2026 - number2
$G$28/14/2026
- number1
B26/29/2026The value in B2.
$G$17/6/2026The value in G1.
MAX(B2,$G$1)46209MAX: Largest number.- number1
B26/29/2026 - number2
$G$17/6/2026
- number1
MIN(C2,$G$2)-MAX(B2,$G$1)2The - operator subtracts.
MIN(C2,$G$2)-MAX(B2,$G$1)+13The + operator adds.
MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1)3MAX: Largest number.- number1
0 - number2
MIN(C2,$G$2)-MAX(B2,$G$1)+13
- number1
In Google Sheets
The same formula works in Google Sheets: =MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1). 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.
| Formula | Result | When to use it |
|---|---|---|
| =MAX(0,NETWORKDAYS(MAX(B2,$G$1),MIN(C2,$G$2))) | 3, 5, 4…Different result | 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) | 3, 5, 4… | 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.
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 the number of overlapping days between two date ranges
D2, count how many days of each employee's leave fall within the project dates in G1:G2, both ends included. Fill down to D6.Questions
How do I just check whether two ranges overlap?
=AND(B2<=$G$2,C2>=$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.
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.