Have you ever encountered a situation that you need to find out how many days in Period 2 fall into Period 1?
Is using IF(IF…(IF…(IF…(IF….. the first thing in your mind?
To find out the number of days between two dates, a simple formula would do:
=EndDate – StartDate +1
- +1 is required to offset the result to include both Start and End days.
When we need to identify number of days overlapped in two periods, it is a little bit tricky as we need to put our maths’ hat on. And the formula could be as short as this:
Yes, the formula works. Then the question is HOW?
As mentioned, it’s all about End Date – Start Date +1. The key is to identify the right Start as well as the right End date.
Let’s take a look at the following diagram for illustration.
Consider the above scenarios, the shadows are the overlapped days for the two periods. Mathematically, they are
- Period 2: End – S
- Period 3: E – Start
- Period 4: E – S
- Period 5: End – Start
Do you see the commonality?
For Period 2 and Period 5, the end date (E) falls after Period 1. Thus End date (End) in Period 1 (which is the smaller one) is required in the calculation. However for Period 3 and Period 4, the end date (E) falls within Period 1, E (which is the smaller one for these scenarios) is thus required.
=MIN(End,E) does the job nicely.
In determining the Start date for the calculation, the same logic applies.
For Period 3 and Period 5, the start date (S) falls before Period 1. Thus Start date (Start) (which is higher) is used for these scenarios. For Period 2 and Period 4, the start date (S) falls within Period 1, S (which is higher) is used for these scenarios.
=MAX(Start,S) does the job nicely.
To summarize in words,
End Dates whichever lower minus Start Dates whichever higher + 1
In Excel, it is
Make sense now?! Wait… it doesn’t work when there is no overlapping day in the two periods.
Good observation! When there is no overlapping in the two periods (see above diagram), MIN(End,E)-Max(Start,S) returns a negative number. This can be tackled by wrapping the formula by MAX(Formula, 0).
Here’s the final formula:
- the number of day(s) overlapped in the two periods; or
- 0 when there is no day overlapped.
“Excel with Maths” could be so helpful! 🙂