How to calculate number of overlapping days for two periods? - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Tuesday, December 24, 2019

How to calculate number of overlapping days for two periods?

How to calculate number of overlapping days for two periods?

Have you ever encountered a situation that you need to find out how many days in Period 2 fall into Period 1?
Excel Tips - How many days overlapped
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:
1
=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.
Excel Tips - How many days overlapped 1
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, (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
1
=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.
Excel Tips - How many days overlapped 2
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:
1
=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! 
#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment