Forum Discussion
How do you get the number of network days one set of dates falls within another?
I have a date range that I need to determine if they fall within another date range and if so, it needs to determine how many net work days overlap within that range. This information would all be on one row of data.
For example:
Date Range 1
G2: 5/21/2021
H2: 6/3/2021
Date Range 2
O2: 4/28/2021
P2: 5/28/2021
G2:H2 falls within the range of O2:P2, but not the full range. I need the formula to ultimately look at these dates and return the number of days that overlap (without counting weekends) so that the end result is 6. Is there a way to get this done just using formulas?
3 Replies
- Forget the last message, I just realized I can accomplish this with a Max(0 before the networkdays equation. Thank you again for all your help, Hans!
HansVogelaar Hello Hans, thank you for your help! This works great for the one scenario, but when applied to a larger data set I'm getting negative values where I should be getting 0 like in the example below since the dates don't overlap at all. Is there a way to have it evaluate to 0 in all scenarios where no overlap occurs rather than a negative value?
G3: 5/21/2021
H3: 6/1/2021
Compared to
O3:6/2/2021
P3:8/17/2021
