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:
=MAX(MIN(C2,C3)-MAX(B2,B3)+1,0)
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
=MIN(End,E)-MAX(Start,S)+1
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:
=MAX(MIN(EndDate1,EndDate2)-MAX(StartDate1,StartDate2)+1,0)
which gives
- 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! 🙂
wmfexcel 
This is a great article. Nicely done, good explanation and good graphics. Thanks.
LikeLike
Thanks for your kind words. Glad you like it. 😀
LikeLike
Hi MF,
I appreciate your straight Mat thought and I have been having problem with some overlapping events: I would like to calculate each even’ts non-overlap minutes from a time series of downtime events.
A generic formula supposed to work for any duration of a time event condition such as multiple events overlap, some, non etc. Up to that point no problem but when total duration of some random events overlap with a single event then I am having issues in terms of a generic excel formulation to calculate non-overlap time of each event. That sheet will be automated so I can’t use different formula for each row since when sheet gets updated all new downtime will be on it. Here is an example:
If you specifically look up 12/14/2017 8:00 am to 12/15/2017 9:00, you can see my point.
Downtime Start (with date) Downtime End (with date)
12/14/2017 6:00 12/14/2017 7:30
12/14/2017 6:30 12/14/2017 7:30
12/14/2017 6:30 12/14/2017 16:00
12/14/2017 7:00 12/14/2017 8:15
12/14/2017 7:30 12/14/2017 8:30
12/14/2017 7:30 12/14/2017 15:00
12/14/2017 7:45 12/14/2017 8:00
12/14/2017 8:00 12/15/2017 9:00
12/14/2017 9:00 12/14/2017 10:00
12/14/2017 11:00 12/15/2017 16:00
Your help would be really appreciated!
Thanks
G.S
LikeLike
Hi G.S
Thanks for your comment.
As you have a very specific question and with example data, may I refer you to one of my favourite Excel forums by Mr. Excel:
https://www.mrexcel.com/forum/excel-questions/
where lots of Excel experts are helping.
Cheers,
LikeLike
Hi , Thanks this is really helpful but what if I have to calculate total number of days n overlapped one should be excluded (means only counted once)
LikeLike
I am not sure if I understood your question correctly. But seems like you want to know how many days between two dates? If so, you may simply substrate Date 1 from Date 2
LikeLike
Hi MF,
Basically I wanted to calculate the all unique dates from multiple dates range avoiding overlapped dates.
example –
If I Have the data as in 3 columns as –
A 02/10/17 03/08/17 (Format is MM/DD/YY)
A 03/04/17 03/07/17
A 04/01/17 04/12/17
B 03/07/17 03/17/17
B ———————
B ————————
Required is-
A – 27 days
B — XXX days
LikeLike
Why 27 days? I still don’t get it.
Anyway, you may try to post your question to Mr. Excel Forum, my all-time favorite:
https://www.mrexcel.com/forum/index.php
Try explore that. Many Excel experts and enthusiasts are helping others with passion.
LikeLike
This is already perfect, do you know a formula that in the end gives me the exact range of data that overlap the two periods?
LikeLike
Pingback: Calculating Work Hours by Hour - Page 2