0

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.

table for date range image

table for holidays image

I tried using networkdays formula for each individual date range. However, it didn't filter out overlapping date range.

Improve this question
2
  • Test if the date is within a range ie before or equal to the end date and after or equal to the start date. What about finding the earliest date and the latest date - that gives you the range... Commented May 5, 2023 at 5:32
  • Please provide enough code so others can better understand or reproduce the problem. Commented May 5, 2023 at 6:25
Related questions
0
Excel Formula to return earliest date and latest date within a date range
1
How calculate business days between dates
651
How can I avoid using Select in Excel VBA?
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.