Discussion Re: How do you get the number of network days one set of dates falls within another? in Excel
https://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2464195#M103783
<P><LI-USER uid="127945"></LI-USER> 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?</P><P> </P><P>G3: 5/21/2021</P><P>H3: 6/1/2021</P><P> </P><P>Compared to</P><P> </P><P>O3:6/2/2021</P><P>P3:8/17/2021</P><P> </P><P> </P>Fri, 18 Jun 2021 19:08:13 GMTAlexie_Rose2021-06-18T19:08:13ZHow do you get the number of network days one set of dates falls within another?
https://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2463957#M103768
<P>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.</P><P> </P><P>For example:</P><P>Date Range 1</P><P>G2: 5/21/2021</P><P>H2: 6/3/2021</P><P> </P><P>Date Range 2</P><P>O2: 4/28/2021</P><P>P2: 5/28/2021</P><P> </P><P>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?</P>Fri, 18 Jun 2021 17:26:10 GMThttps://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2463957#M103768Alexie_Rose2021-06-18T17:26:10ZRe: How do you get the number of network days one set of dates falls within another?
https://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2464127#M103777
<P><LI-USER uid="1082601"></LI-USER> </P>
<P> </P>
<P>=NETWORKDAYS(MAX(G2,O2),MIN(H2,P2))</P>Fri, 18 Jun 2021 18:39:42 GMThttps://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2464127#M103777Hans Vogelaar2021-06-18T18:39:42ZRe: How do you get the number of network days one set of dates falls within another?
https://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2464195#M103783
<P><LI-USER uid="127945"></LI-USER> 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?</P><P> </P><P>G3: 5/21/2021</P><P>H3: 6/1/2021</P><P> </P><P>Compared to</P><P> </P><P>O3:6/2/2021</P><P>P3:8/17/2021</P><P> </P><P> </P>Fri, 18 Jun 2021 19:08:13 GMThttps://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2464195#M103783Alexie_Rose2021-06-18T19:08:13ZRe: How do you get the number of network days one set of dates falls within another?
https://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2464246#M103786
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!Fri, 18 Jun 2021 19:28:55 GMThttps://techcommunity.microsoft.com/t5/excel/how-do-you-get-the-number-of-network-days-one-set-of-dates-falls/m-p/2464246#M103786Alexie_Rose2021-06-18T19:28:55Z