Need help on next step

Z

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Workdays Formula (Specific)
Replies
7
Views
430
felixstraube
F
S
  • Solved
Networkdays formula issue
Replies
3
Views
354
Joe4
Joe4
A
  • Solved
NETWORKDAYS vs COUNTIFS
Replies
2
Views
510
amberd
A
F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
204
Peter_SSs
Peter_SSs
V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
114
djclements
djclements

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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