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


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , . Bookmark the permalink.

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

  1. AR says:

    This is a great article. Nicely done, good explanation and good graphics. Thanks.


  2. GS says:

    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!




  3. 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)


    • 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


      • 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


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


  5. 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.