SOLVED

How do you get the number of network days one set of dates falls within another?

%3CLINGO-SUB%20id%3D%22lingo-sub-2463957%22%20slang%3D%22en-US%22%3EHow%20do%20you%20get%20the%20number%20of%20network%20days%20one%20set%20of%20dates%20falls%20within%20another%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2463957%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20date%20range%20that%20I%20need%20to%20determine%20if%20they%20fall%20within%20another%20date%20range%20and%20if%20so%2C%20it%20needs%20to%20determine%20how%20many%20net%20work%20days%20overlap%20within%20that%20range.%20This%20information%20would%20all%20be%20on%20one%20row%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EDate%20Range%201%3C%2FP%3E%3CP%3EG2%3A%205%2F21%2F2021%3C%2FP%3E%3CP%3EH2%3A%26nbsp%3B%206%2F3%2F2021%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDate%20Range%202%3C%2FP%3E%3CP%3EO2%3A%204%2F28%2F2021%3C%2FP%3E%3CP%3EP2%3A%205%2F28%2F2021%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EG2%3AH2%20falls%20within%20the%20range%20of%20O2%3AP2%2C%20but%20not%20the%20full%20range.%20I%20need%20the%20formula%20to%20ultimately%20look%20at%20these%20dates%20and%20return%20the%20number%20of%20days%20that%20overlap%20(without%20counting%20weekends)%20so%20that%20the%20end%20result%20is%206.%20Is%20there%20a%20way%20to%20get%20this%20done%20just%20using%20formulas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2463957%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464127%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20you%20get%20the%20number%20of%20network%20days%20one%20set%20of%20dates%20falls%20within%20another%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1082601%22%20target%3D%22_blank%22%3E%40Alexie_Rose%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DNETWORKDAYS(MAX(G2%2CO2)%2CMIN(H2%2CP2))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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
best response confirmed by Alexie_Rose (New Contributor)
Solution

@Alexie_Rose 

 

=NETWORKDAYS(MAX(G2,O2),MIN(H2,P2))

@Hans Vogelaar 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

 

 

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!