Need help on next step

[zzbhs]

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).

Beg	Span:	8/25/14	Cycle:	8/25/14	10/6/14	11/10/14	1/5/15	2/23/15	4/20/15
End		5/29/15		10/3/14	11/7/14	12/23/14	2/20/15	4/17/15	5/29/15
Count		178		29	25	29	32	35	28
				1st	2nd	3rd	4th	5th	6th
Inv1	10/31/14	12/22/14		0	6	27	0	0	0
Inv2	2/18/15	4/2/15		0	0	0	3	23	0

<tbody>
</tbody>

 

[Tetra201]

Could you please clarify how you arrived at 27 for the 3rd cycle and 23 for the 5th cycle?

 

[zzbhs]

Given a Holiday Table of

Holiday	9/1/14	11/26/14	11/27/14	11/28/14	12/24/14	12/25/14
	12/30/14	12/31/14	1/1/15	1/2/15	1/19/15	1/22/15
	3/17/15	3/18/15	3/19/15	3/20/15	5/22/15	5/25/15

<colgroup><col width="64" style="width: 48pt;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" span="6"> <tbody> </tbody>

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

	Mon 10/27/2014	2nd 6 Wk	16
	Tue 10/28/2014	2nd 6 Wk	17
	Wed 10/29/2014	2nd 6 Wk	18
	Thu 10/30/2014	2nd 6 Wk	19
	Fri 10/31/2014	2nd 6 Wk	20	1
	Mon 11/3/2014	2nd 6 Wk	21	2
	Tue 11/4/2014	2nd 6 Wk	22	3
	Wed 11/5/2014	2nd 6 Wk	23	4
	Thu 11/6/2014	2nd 6 Wk	24	5
	Fri 11/7/2014	2nd 6 Wk	25	6
	Mon 11/10/2014	3rd 6 Wk	1	1
	Tue 11/11/2014	3rd 6 Wk	2	2
	Wed 11/12/2014	3rd 6 Wk	3	3
	Thu 11/13/2014	3rd 6 Wk	4	4
	Fri 11/14/2014	3rd 6 Wk	5	5
	Mon 11/17/2014	3rd 6 Wk	6	6
	Tue 11/18/2014	3rd 6 Wk	7	7
	Wed 11/19/2014	3rd 6 Wk	8	8
	Thu 11/20/2014	3rd 6 Wk	9	9
	Fri 11/21/2014	3rd 6 Wk	10	10
	Mon 11/24/2014	3rd 6 Wk	11	11
	Tue 11/25/2014	3rd 6 Wk	12	12
	Wed 11/26/2014	Holiday
	Thu 11/27/2014	Holiday
	Fri 11/28/2014	Holiday
	Mon 12/1/2014	3rd 6 Wk	13	13
	Tue 12/2/2014	3rd 6 Wk	14	14
	Wed 12/3/2014	3rd 6 Wk	15	15
	Thu 12/4/2014	3rd 6 Wk	16	16
	Fri 12/5/2014	3rd 6 Wk	17	17
	Mon 12/8/2014	3rd 6 Wk	18	18
	Tue 12/9/2014	3rd 6 Wk	19	19
	Wed 12/10/2014	3rd 6 Wk	20	20
	Thu 12/11/2014	3rd 6 Wk	21	21
	Fri 12/12/2014	3rd 6 Wk	22	22
	Mon 12/15/2014	3rd 6 Wk	23	23
	Tue 12/16/2014	3rd 6 Wk	24	24
	Wed 12/17/2014	3rd 6 Wk	25	25
	Thu 12/18/2014	3rd 6 Wk	26	26
	Fri 12/19/2014	3rd 6 Wk	27	27
	Mon 12/22/2014	3rd 6 Wk	28	W/draw date
	Tue 12/23/2014	3rd 6 Wk	29

<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> </tbody>

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Individual 2

	Mon 2/16/2015	Holiday
	Tue 2/17/2015	4th 6 WK	29
	Wed 2/18/2015	4th 6 WK	30		1
	Thu 2/19/2015	4th 6 WK	31		2
	Fri 2/20/2015	4th 6 WK	32		3
	Mon 2/23/2015	5th 6 Wk	1		1
	Tue 2/24/2015	5th 6 Wk	2		2
	Wed 2/25/2015	5th 6 Wk	3		3
	Thu 2/26/2015	5th 6 Wk	4		4
	Fri 2/27/2015	5th 6 Wk	5		5
	Mon 3/2/2015	5th 6 Wk	6		6
	Tue 3/3/2015	5th 6 Wk	7		7
	Wed 3/4/2015	5th 6 Wk	8		8
	Thu 3/5/2015	5th 6 Wk	9		9
	Fri 3/6/2015	5th 6 Wk	10		10
	Mon 3/9/2015	5th 6 Wk	11		11
	Tue 3/10/2015	5th 6 Wk	12		12
	Wed 3/11/2015	5th 6 Wk	13		13
	Thu 3/12/2015	5th 6 Wk	14		14
	Fri 3/13/2015	5th 6 Wk	15		15
	Mon 3/16/2015	Holiday
	Tue 3/17/2015	Holiday
	Wed 3/18/2015	Holiday
	Thu 3/19/2015	Holiday
	Fri 3/20/2015	Holiday
	Mon 3/23/2015	5th 6 Wk	16		16
	Tue 3/24/2015	5th 6 Wk	17		17
	Wed 3/25/2015	5th 6 Wk	18		18
	Thu 3/26/2015	5th 6 Wk	19		19
	Fri 3/27/2015	5th 6 Wk	20		20
	Mon 3/30/2015	5th 6 Wk	21		21
	Tue 3/31/2015	5th 6 Wk	22		22
	Wed 4/1/2015	5th 6 Wk	23		23
	Thu 4/2/2015	5th 6 Wk	24		W/draw date
	Fri 4/3/2015	5th 6 Wk	25
	Mon 4/6/2015	5th 6 Wk	26
	Tue 4/7/2015	5th 6 Wk	27

<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> </tbody>

 

[Tetra201]

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))

 

[zzbhs]

A homerun -- Thank you so much. Never worked with Max Min before got to look into those more now.
Thank you again.

 

[Tetra201]

You are welcome.