Need help on next step

zzbhs

New Member
Joined
Apr 24, 2017
Messages
3
I've used the NETWORKDAYS to count my calendar span (beg/end) including setting up a holiday table. Within this calendar span there are 6 cycle periods and I've used NETWORKDAYS to calculate the day counts per each cycle. The per cycles days counts total to the span total.

The next step issue -- I have a list of individuals with start dates and end dates (withdraw dates) all within that calendar span; but these dates may cover multiple cycles for each individuals. Given these individual's start and end dates I need to know that individual's total day count in EACH of the CYCLEs in the SPAN. Note the end date does not count as a day count. Hoping for help on a formula.

The example below on row 3 the =NETWORKDAYS(Beg, End, [holidaytable]) was used to get the day count. Need a formula to count days listed by individuals - Per each of the 6 cycles. (NOTE: The 6, 27, 3 and 23 were hand calculated and entered; this is too show what a formula should return).

[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD]Beg[/TD]
[TD="align: right"]Span:[/TD]
[TD]8/25/14[/TD]
[TD="align: right"]Cycle:[/TD]
[TD]8/25/14[/TD]
[TD]10/6/14[/TD]
[TD]11/10/14[/TD]
[TD]1/5/15[/TD]
[TD]2/23/15[/TD]
[TD]4/20/15[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD][/TD]
[TD]5/29/15[/TD]
[TD][/TD]
[TD]10/3/14[/TD]
[TD]11/7/14[/TD]
[TD]12/23/14[/TD]
[TD]2/20/15[/TD]
[TD]4/17/15[/TD]
[TD]5/29/15[/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD][/TD]
[TD]178[/TD]
[TD][/TD]
[TD]29[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD]32[/TD]
[TD]35[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]4th[/TD]
[TD]5th[/TD]
[TD]6th[/TD]
[/TR]
[TR]
[TD]Inv1[/TD]
[TD]10/31/14[/TD]
[TD]12/22/14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]27[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Inv2[/TD]
[TD]2/18/15[/TD]
[TD]4/2/15[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]23[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Could you please clarify how you arrived at 27 for the 3rd cycle and 23 for the 5th cycle?
 
Upvote 0
Given a Holiday Table of

[TABLE="width: 372"]
<colgroup><col width="64" style="width: 48pt;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" span="6"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Holiday[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]9/1/14[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]11/26/14[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]11/27/14[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]11/28/14[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]12/24/14[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]12/25/14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12/30/14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12/31/14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/1/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/2/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/19/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/22/15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3/17/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3/18/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3/19/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3/20/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5/22/15[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5/25/15[/TD]
[/TR]
</tbody>[/TABLE]

To get the breakdown by cycles I listed the dates in each cycle down a worksheet then counted the days from Inv 1 & 2 start date to end date. See cut out section below -- which easily shows why I'm asking for help on a formula instead of this. Thanks for looking.

Individual 1 Date Cycle Day in Cycle Indv Days
[TABLE="width: 321"]
<colgroup><col width="64" style="width: 48pt;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 108, bgcolor: #E6B8B7, align: right"]Mon 10/27/2014[/TD]
[TD="class: xl69, width: 64, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Tue 10/28/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Wed 10/29/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Thu 10/30/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Fri 10/31/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Mon 11/3/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Tue 11/4/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Wed 11/5/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Thu 11/6/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #E6B8B7, align: right"]Fri 11/7/2014[/TD]
[TD="class: xl69, bgcolor: #E6B8B7"]2nd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Mon 11/10/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Tue 11/11/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Wed 11/12/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Thu 11/13/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Fri 11/14/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Mon 11/17/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Tue 11/18/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Wed 11/19/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Thu 11/20/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Fri 11/21/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Mon 11/24/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Tue 11/25/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Wed 11/26/2014[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Thu 11/27/2014[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Fri 11/28/2014[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Mon 12/1/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Tue 12/2/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Wed 12/3/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Thu 12/4/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Fri 12/5/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Mon 12/8/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Tue 12/9/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Wed 12/10/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Thu 12/11/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Fri 12/12/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Mon 12/15/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Tue 12/16/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Wed 12/17/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Thu 12/18/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]26[/TD]
[TD="bgcolor: transparent, align: right"]26[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Fri 12/19/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]27[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Mon 12/22/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 2"]W/draw date [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #B1A0C7, align: right"]Tue 12/23/2014[/TD]
[TD="class: xl71, bgcolor: #B1A0C7"]3rd 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Individual 2
[TABLE="width: 369"]
<colgroup><col width="64" style="width: 48pt;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="64" style="width: 48pt;" span="5"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 108, bgcolor: red, align: right"]Mon 2/16/2015[/TD]
[TD="class: xl66, width: 64, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, width: 64, bgcolor: red"] [/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #C4BD97, align: right"]Tue 2/17/2015[/TD]
[TD="class: xl71, bgcolor: #C4BD97"]4th 6 WK[/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #C4BD97, align: right"]Wed 2/18/2015[/TD]
[TD="class: xl71, bgcolor: #C4BD97"]4th 6 WK[/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #C4BD97, align: right"]Thu 2/19/2015[/TD]
[TD="class: xl71, bgcolor: #C4BD97"]4th 6 WK[/TD]
[TD="bgcolor: transparent, align: right"]31[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #C4BD97, align: right"]Fri 2/20/2015[/TD]
[TD="class: xl71, bgcolor: #C4BD97"]4th 6 WK[/TD]
[TD="bgcolor: transparent, align: right"]32[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Mon 2/23/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Tue 2/24/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Wed 2/25/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Thu 2/26/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Fri 2/27/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Mon 3/2/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Tue 3/3/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Wed 3/4/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Thu 3/5/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Fri 3/6/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Mon 3/9/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Tue 3/10/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Wed 3/11/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Thu 3/12/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Fri 3/13/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Mon 3/16/2015[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Tue 3/17/2015[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Wed 3/18/2015[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Thu 3/19/2015[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: red, align: right"]Fri 3/20/2015[/TD]
[TD="class: xl66, bgcolor: red"]Holiday[/TD]
[TD="class: xl67, bgcolor: red"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Mon 3/23/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Tue 3/24/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Wed 3/25/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Thu 3/26/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Fri 3/27/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Mon 3/30/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Tue 3/31/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Wed 4/1/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Thu 4/2/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 2"]W/draw date [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Fri 4/3/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Mon 4/6/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]26[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: #CC99FF, align: right"]Tue 4/7/2015[/TD]
[TD="class: xl69, bgcolor: #CC99FF"]5th 6 Wk[/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Assuming your dataset from Post #1 covers range A1:J6 and your holiday dates are in a range named Holidays, try the following formula:

=MAX(0,NETWORKDAYS(MAX(E$1,$B5),MIN(E$2,$C5-1),Holidays))
 
Upvote 0
A homerun -- Thank you so much. Never worked with Max Min before got to look into those more now.
Thank you again.
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top