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:

=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, 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.

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:

=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! 🙂

Advertisements
This entry was posted in Formula and tagged , , . Bookmark the permalink.

6 Responses to How to calculate number of overlapping days for two periods?

  1. Parag Madpuwar says:

    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)

    Like

    • MF says:

      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

      Like

      • Parag Madpuwar says:

        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

        Like

  2. Margo says:

    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?

    Like

  3. Pingback: Calculating Work Hours by Hour - Page 2

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s