Workdays that fall in a time period of 1 year between date criteria

[dlatha01]

I’m trying to work out a formula to determine how many workdays (networkdays) fall within the 1Y date range in C1-D1, that fall in between the non trade periods.

So, for example, in Non trade period, in A2-B2, it would be 262 workdays as Non trade period covers the time period in C1-D1, for A3-B3, it would be 0, for A4-B4, 198, and A5-B5 would be 205.

Hope that makes sense, thanks for your help.


Time Period (1Y)
in C1 - 30/05/2013
in D1 – 31/05/2014

Non Trade Period
In A2 - 19/04/2011
In B2 - 24/11/2014

In A3 – 10/05/2013
In B3 – 29/05/2013

In A4 – 28/08/2013
In B4 – 17/06/2014

In A5 – 21/11/2013
In B5 – 12/03/2014

 

[barry houdini]

I think you would only get 205 for the last example if A5 is 21/11/2012 ....but if you use this formula in C2 copied down you should get the required results for the others

=MAX(0,NETWORKDAYS(MAX(C$1,A2),MIN(D$1,B2)))

 

[dlatha01]

Brilliant, thank you so much ! very helpful