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:


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


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:


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

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

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

  1. 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?


  2. 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: Logo

You are commenting using your 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