Share via

How to calculate the total of non-overlapping business days in a date range in excel using formula?

Anonymous
2023-05-05T00:53:56+00:00

Hi,

I would like to calculate the total non-overlapping business days in two date range columns that have partially or fully overlapping date ranges with each other. An example is pasted below.

For instance, the date range of May 1, 2023 - May 2, 2023 overlaps with May 1, 2023 - May 12, 2023. I would like to count 10 business days instead of 12. The date range of May 23, 2023 - May 29, 2023 partially overlaps with May 20, 2023 - May 25, 2023 and May 25, 2023 - May 31, 2023. I would like to count 7 business days instead of 13. In total, the sum should be 17 business days instead of 25 business days or 29 calendar days.

The business days will filter out the holidays listed below.

Many thanks for your help.

start End Total Calendar Days Total Business Days Wanted Non-overlapping Business Days
2023-05-01 2023-05-02 1 2
2023-05-01 2023-05-12 11 10
2023-05-23 2023-05-29 6 5
2023-05-20 2023-05-25 5 3
2023-05-25 2023-05-31 6 5
Min and Max date range 2023-05-01 2023-05-31 29 25 17
Date Holiday
2023-01-02 New Year's Day
2023-02-20 Family Day
2023-02-20 Islander Day
2023-02-20 Louis Riel Day
2023-02-20 Nova Scotia Heritage Day
2023-03-20 Saint Patrick's Day
2023-04-07 Good Friday
2023-04-10 Easter Monday
2023-04-24 Saint George's Day
2023-05-22 National Patriots' Day
2023-05-22 Victoria Day
2023-06-21 National Indigenous Peoples Day
2023-06-26 June Holiday
2023-07-03 Canada Day
2023-07-09 Nunavut Day
2023-07-10 Orangeman's Day
2023-08-07 Civic Holiday
2023-08-07 Saskatchewan Day
2023-08-07 New Brunswick Day
2023-08-07 B.C. Day
2023-08-07 Alberta Heritage Day
2023-08-21 Discovery Day
2023-09-04 Labour Day
2023-09-30 National Day for Truth and Reconciliation
2023-10-09 Thanksgiving Day
2023-11-11 Remembrance Day
2023-12-25 Christmas Day
2023-12-26 Boxing Day
Microsoft 365 and Office | Excel | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-06T02:14:11+00:00

    Try it again.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-05-05T18:51:22+00:00

    Hello Snow Lu MSFT,

    I don't seem to have that private message function. A screenshot is pasted below.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-05-05T17:42:37+00:00

    Could you share a test file to me? You may share it via private message by clicking the icon below. I will try it next week.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2023-05-05T17:27:50+00:00

    Thank you very much for your response Snow Lu MSFT.

    This formula is great but it appears to only work for 1 month in a year. I’m using the formula for a template. So I might have 200 start dates and end dates with blank cells. The user might populate any day, month, and year between 2023 to 2030 for instance, not just May 2023.

    What formula should I use to calculate the total non-overlapping business days in two date range columns that have partially or fully overlapping date ranges with each other in this case?

    Also, is the sequence in the formula needed for 200 rows or is there another way?

    A brief example is attached below.

    Start End Holidays
    2023-05-01 2023-05-02 January 2, 2023
    2023-05-01 2023-05-12 April 7, 2023
    2024-05-23 2024-05-29 April 10, 2023
    2024-05-20 2024-05-25 May 22, 2023
    2026-05-25 2026-05-31 July 3, 2023
    August 7, 2023
    September 4, 2023
    October 2, 2023
    October 9, 2023
    November 13, 2023
    December 25, 2023
    December 26, 2023
    January 1, 2024
    March 29, 2024
    April 1, 2024
    May 20, 2024
    July 1, 2024
    August 5, 2024
    September 21, 2024
    September 30, 2024
    October 14, 2024
    November 11, 2024
    December 25, 2024
    December 26, 2024
    January 1, 2025
    April 18, 2025
    April 21, 2025
    May 19, 2025
    July 1, 2025
    August 4, 2025
    September 1, 2025
    September 30, 2025
    October 13, 2025
    November 11, 2025
    December 25, 2025
    December 26, 2025
    January 1, 2026
    April 3, 2026
    April 6, 2026
    May 18, 2026
    July 1, 2026
    August 3, 2026
    September 7, 2026
    September 30, 2026
    2024-05-23 2024-05-29 October 12, 2026
    2024-05-20 2024-05-25 November 11, 2026
    2026-05-25 2026-05-31 December 25, 2026
    December 28, 2026

    Was this answer helpful?

    0 comments
  5. Anonymous
    2023-05-05T07:13:31+00:00

    You can use this formula to count Non-overlapping business days. Then substact the holiday.

    =SUM(IF(WEEKDAY(DATE(2023,5,UNIQUE(TRANSPOSE(HSTACK(SEQUENCE(1,B2-A2+1,DAY(A2),1),SEQUENCE(1,B3-A3+1,DAY(A3),1),SEQUENCE(1,B4-A4+1,DAY(A4),1),SEQUENCE(1,B5-A5+1,DAY(A5),1),SEQUENCE(1,B6-A6+1,DAY(A6),1))))),2)<6,1,0))

    Count holidays

    =SUM(IF(COUNTIF(A12:A40,DATE(2023,5,UNIQUE(TRANSPOSE(HSTACK(SEQUENCE(1,B2-A2+1,DAY(A2),1),SEQUENCE(1,B3-A3+1,DAY(A3),1),SEQUENCE(1,B4-A4+1,DAY(A4),1),SEQUENCE(1,B5-A5+1,DAY(A5),1),SEQUENCE(1,B6-A6+1,DAY(A6),1))))))>0,1))

    Final Result

    =SUM(IF(WEEKDAY(DATE(2023,5,UNIQUE(TRANSPOSE(HSTACK(SEQUENCE(1,B2-A2+1,DAY(A2),1),SEQUENCE(1,B3-A3+1,DAY(A3),1),SEQUENCE(1,B4-A4+1,DAY(A4),1),SEQUENCE(1,B5-A5+1,DAY(A5),1),SEQUENCE(1,B6-A6+1,DAY(A6),1))))),2)<6,1,0))-SUM(IF(COUNTIF(A12:A40,DATE(2023,5,UNIQUE(TRANSPOSE(HSTACK(SEQUENCE(1,B2-A2+1,DAY(A2),1),SEQUENCE(1,B3-A3+1,DAY(A3),1),SEQUENCE(1,B4-A4+1,DAY(A4),1),SEQUENCE(1,B5-A5+1,DAY(A5),1),SEQUENCE(1,B6-A6+1,DAY(A6),1))))))>0,1))

    You may check the file via private message by clicking the icon below.

    Was this answer helpful?

    0 comments