Dynamic offset?

[GWard02]

I have a "schedule" used for production. We start a boat on a spray date and have the boats in a sequence (14 per day). The work schedule is Mon-Thu, though we work some Fridays and have some Monday holidays. I need to create a schedule for Engine Delivery based off this schedule. Engines get delivered 36 slots (sequenced boats) after spray. So, a boat that was sprayed/started in slot 8 on 2/6/2013 would be the first engine delivered on 2/11/2013. I've used this site before (and the MrExcel youtube channel) to build dynamic array formulas before, however I am stuck on this one and can't figure out how to construct it. The challenges are determining which days are production (people are at work) on the spray date and on the subsequent offset date. When those two don't line up, I don't know how to continue the offset and skip the unneeded rows. The days around the spray date 2/15 (non-production day) are a good example of where the trouble comes in.

Spray date is the start date of production for that boat. Code is a concatenation of spray date and sequence number. boat is just an identifier for each specific boat. The "Eng" columns are what I need to return (with manually filled in values). The specific value I need to return is the Eng Delivery Date Code because I will look that up from another sheet so we can print labels. The other "Eng Del" columns were just there for visual clues while working through it. The offset is an example on the one boat for which date/seq num it needed to return for engine delivery. I've only included a subset (skipping rows etc) in the html table. The full data is below.

Spray Date	Code	Boat	Seq Num	Eng Del Dt	Eng Del Dt Code	Eng Del Seq num	offset
2/6/2013	41311:14	T-7460	14	2/11/2013	41316:7	7
2/7/2013	41312:1	K-7461	1	2/11/2013	41316:8	8
2/7/2013	41312:2	A-1594	2	2/11/2013	41316:9	9
2/7/2013	41312:3	Y-7462	3	2/11/2013	41316:10	10
2/7/2013	41312:4	T-7463	4	2/11/2013	41316:11	11
2/7/2013	41312:5	F-7464	5	2/11/2013	41316:12	12
2/7/2013	41312:6	B-1595	6	2/11/2013	41316:13	13
2/7/2013	41312:7	S-7465	7	2/11/2013	41316:14	14
2/7/2013	41312:8	K-7466	8	2/12/2013	41317:1	1
2/14/2013	41319:14	K7525	14	2/20/2013	41325:7	7
2/15/2013	41320:1	#NA	1
		no productionthisday
2/18/2013	41323:1	S-7528	1	2/20/2013	41325:8	8

<tbody>
</tbody>

any guidance is appreciated!

Here is the actual data:

Spray Date	Code	Boat	Boat Number	Engine Delivery Date	Engine Delivery Date Code	Engine Delivery Sequence
2/4/2013	41309:1	X-7426	1
2/4/2013	41309:2	K-7427	2
2/4/2013	41309:3	L-7428	3
2/4/2013	41309:4	T-7429	4
2/4/2013	41309:5	K-7430	5
2/4/2013	41309:6	A-1586	6
2/4/2013	41309:7	U-7431	7
2/4/2013	41309:8	T-7432	8			1
2/4/2013	41309:9	F-7433	9			2
2/4/2013	41309:10	B-1587	10			3
2/4/2013	41309:11	S-7434	11			4
2/4/2013	41309:12	K-7435	12			5
2/4/2013	41309:13	S-7436	13			6
2/4/2013	41309:14	A-1588	14			7
			15
			16
2/5/2013	41310:1	W-7437	1			8
2/5/2013	41310:2	M-7438	2			9
2/5/2013	41310:3	S-7439	3			10
2/5/2013	41310:4	A-1589	4			11
2/5/2013	41310:5	S-7440	5			12
2/5/2013	41310:6	X-7441	6			13
2/5/2013	41310:7	K-7442	7			14
2/5/2013	41310:8	L-7444	8			1
2/5/2013	41310:9	T-7445	9			2
2/5/2013	41310:10	K-7446	10			3
2/5/2013	41310:11	A-1590	11			4
2/5/2013	41310:12	U-7447	12			5
2/5/2013	41310:13	T-7448	13			6
2/5/2013	41310:14	F-7449	14			7
			15
			16
2/6/2013	41311:1	B-1591	1			8
2/6/2013	41311:2	S-7450	2			9
2/6/2013	41311:3	K-7451	3			10
2/6/2013	41311:4	S-7452	4			11
2/6/2013	41311:5	A-1592	5			12
2/6/2013	41311:6	W-7453	6			13
2/6/2013	41311:7	M-7454	7			14	Offset
2/6/2013	41311:8	S-7455	8	2/11/2013	41316:1	1	1
2/6/2013	41311:9	A-1593	9	2/11/2013	41316:2	2	2
2/6/2013	41311:10	S-7456	10	2/11/2013	41316:3	3	3
2/6/2013	41311:11	X-7457	11	2/11/2013	41316:4	4	4
2/6/2013	41311:12	K-7458	12	2/11/2013	41316:5	5	5
2/6/2013	41311:13	L-7459	13	2/11/2013	41316:6	6	6
2/6/2013	41311:14	T-7460	14	2/11/2013	41316:7	7	7
			15
			16
2/7/2013	41312:1	K-7461	1	2/11/2013	41316:8	8	8
2/7/2013	41312:2	A-1594	2	2/11/2013	41316:9	9	9
2/7/2013	41312:3	Y-7462	3	2/11/2013	41316:10	10	10
2/7/2013	41312:4	T-7463	4	2/11/2013	41316:11	11	11
2/7/2013	41312:5	F-7464	5	2/11/2013	41316:12	12	12
2/7/2013	41312:6	B-1595	6	2/11/2013	41316:13	13	13
2/7/2013	41312:7	S-7465	7	2/11/2013	41316:14	14	14
2/7/2013	41312:8	K-7466	8	2/12/2013	41317:1	1	15
2/7/2013	41312:9	S-7467	9	2/12/2013	41317:2	2	16
2/7/2013	41312:10	A-1596	10	2/12/2013	41317:3	3	17
2/7/2013	41312:11	W-7468	11	2/12/2013	41317:4	4	18
2/7/2013	41312:12	M-7469	12	2/12/2013	41317:5	5	19
2/7/2013	41312:13	S-7470	13	2/12/2013	41317:6	6	20
2/7/2013	41312:14	A-1597	14	2/12/2013	41317:7	7	21
			15
			16
2/8/2013	41313:1	S-7471	1	2/12/2013	41317:8	8	22
2/8/2013	41313:2	X-7472	2	2/12/2013	41317:9	9	23
2/8/2013	41313:3	K-7473	3	2/12/2013	41317:10	10	24
2/8/2013	41313:4	L-7474	4	2/12/2013	41317:11	11	25
2/8/2013	41313:5	T-7475	5	2/12/2013	41317:12	12	26
2/8/2013	41313:6	K-7476	6	2/12/2013	41317:13	13	27
2/8/2013	41313:7	A-1598	7	2/12/2013	41317:14	14	28
2/8/2013	41313:8	Y-7477	8	2/13/2013	41318:1	1	29
2/8/2013	41313:9	K-7478	9	2/13/2013	41318:2	2	30
2/8/2013	41313:10	F-7479	10	2/13/2013	41318:3	3	31
2/8/2013	41313:11	B-1599	11	2/13/2013	41318:4	4	32
2/8/2013	41313:12	S-7480	12	2/13/2013	41318:5	5	33
2/8/2013	41313:13	K-7481	13	2/13/2013	41318:6	6	34
2/8/2013	41313:14	S-7482	14	2/13/2013	41318:7	7	35
			15
			16
2/11/2013	41316:1	A-1600	1	2/13/2013	41318:8	8	36
2/11/2013	41316:2	W-7483	2	2/13/2013	41318:9	9
2/11/2013	41316:3	M-7484	3	2/13/2013	41318:10	10
2/11/2013	41316:4	S-7485	4	2/13/2013	41318:11	11
2/11/2013	41316:5	A-1601	5	2/13/2013	41318:12	12
2/11/2013	41316:6	S-7486	6	2/13/2013	41318:13	13
2/11/2013	41316:7	X-7487	7	2/13/2013	41318:14	14
2/11/2013	41316:8	K-7488	8	2/14/2013	41319:1	1
2/11/2013	41316:9	L-7489	9	2/14/2013	41319:2	2
2/11/2013	41316:10	T-7490	10	2/14/2013	41319:3	3
2/11/2013	41316:11	K-7491	11	2/14/2013	41319:4	4
2/11/2013	41316:12	A-1602	12	2/14/2013	41319:5	5
2/11/2013	41316:13	M-7492	13	2/14/2013	41319:6	6
2/11/2013	41316:14	K-7443	14	2/14/2013	41319:7	7
			15
			16
2/12/2013	41317:1	F-7493	1	2/14/2013	41319:8	8
2/12/2013	41317:2	B-1603	2	2/14/2013	41319:9	9
2/12/2013	41317:3	S-7494	3	2/14/2013	41319:10	10
2/12/2013	41317:4	K-7495	4	2/14/2013	41319:11	11
2/12/2013	41317:5	S-7496	5	2/14/2013	41319:12	12
2/12/2013	41317:6	A-1604	6	2/14/2013	41319:13	13
2/12/2013	41317:7	W-7497	7	2/14/2013	41319:14	14
2/12/2013	41317:8	M-7498	8	2/18/2013	41323:1	1
2/12/2013	41317:9	S-7499	9	2/18/2013	41323:2	2
2/12/2013	41317:10	A-1629	10	2/18/2013	41323:3	3
2/12/2013	41317:11	S-7500	11	2/18/2013	41323:4	4
2/12/2013	41317:12	X-7501	12	2/18/2013	41323:5	5
2/12/2013	41317:13	K-7502	13	2/18/2013	41323:6	6
2/12/2013	41317:14	L-7503	14	2/18/2013	41323:7	7
			15
			16
2/13/2013	41318:1	T-7504	1	2/18/2013	41323:8	8
2/13/2013	41318:2	K-7505	2	2/18/2013	41323:9	9
2/13/2013	41318:3	A-1606	3	2/18/2013	41323:10	10
2/13/2013	41318:4	M-7506	4	2/18/2013	41323:11	11
2/13/2013	41318:5	K-7507	5	2/18/2013	41323:12	12
2/13/2013	41318:6	F-7508	6	2/18/2013	41323:13	13
2/13/2013	41318:7	B-1607	7	2/18/2013	41323:14	14
2/13/2013	41318:8	S-7509	8	2/19/2013	41324:1	1
2/13/2013	41318:9	K-7510	9	2/19/2013	41324:2	2
2/13/2013	41318:10	S-7511	10	2/19/2013	41324:3	3
2/13/2013	41318:11	A-1608	11	2/19/2013	41324:4	4
2/13/2013	41318:12	W-7512	12	2/19/2013	41324:5	5
2/13/2013	41318:13	M-7513	13	2/19/2013	41324:6	6
2/13/2013	41318:14	S-7514	14	2/19/2013	41324:7	7
			15
			16
2/14/2013	41319:1	A-1609	1	2/19/2013	41324:8	8
2/14/2013	41319:2	S-7515	2	2/19/2013	41324:9	9
2/14/2013	41319:3	X-7516	3	2/19/2013	41324:10	10
2/14/2013	41319:4	K-7526	4	2/19/2013	41324:11	11
2/14/2013	41319:5	L-7518	5	2/19/2013	41324:12	12
2/14/2013	41319:6	T-7519	6	2/19/2013	41324:13	13
2/14/2013	41319:7	K-7520	7	2/19/2013	41324:14	14
2/14/2013	41319:8	A-1610	8	2/20/2013	41325:1	1
2/14/2013	41319:9	M-7521	9	2/20/2013	41325:2	2
2/14/2013	41319:10	K-7522	10	2/20/2013	41325:3	3
2/14/2013	41319:11	F-7523	11	2/20/2013	41325:4	4
2/14/2013	41319:12	B-1611	12	2/20/2013	41325:5	5
2/14/2013	41319:13	S-7524	13	2/20/2013	41325:6	6
2/14/2013	41319:14	K-7525	14	2/20/2013	41325:7	7
			15
			16
2/15/2013	41320:1	#N/A	1
2/15/2013	41320:2	#N/A	2
2/15/2013	41320:3	#N/A	3
2/15/2013	41320:4	#N/A	4
2/15/2013	41320:5	#N/A	5
2/15/2013	41320:6	#N/A	6
2/15/2013	41320:7	#N/A	7
2/15/2013	41320:8	#N/A	8
2/15/2013	41320:9	#N/A	9
2/15/2013	41320:10	#N/A	10
2/15/2013	41320:11	#N/A	11
2/15/2013	41320:12	#N/A	12
2/15/2013	41320:13	#N/A	13
2/15/2013	41320:14	#N/A	14
			15
			16
2/18/2013	41323:1	S-7528	1	2/20/2013	41325:8	8
2/18/2013	41323:2	A-1613	2	2/20/2013	41325:9	9
2/18/2013	41323:3	W-7529	3	2/20/2013	41325:10	10
2/18/2013	41323:4	M-7530	4	2/20/2013	41325:11	11
2/18/2013	41323:5	S-7531	5	2/20/2013	41325:12	12
2/18/2013	41323:6	A-1614	6	2/20/2013	41325:13	13
2/18/2013	41323:7	S-7532	7	2/20/2013	41325:14	14
2/18/2013	41323:8	X-7533	8	2/21/2013	41326:1	1
2/18/2013	41323:9	K-7534	9	2/21/2013	41326:2	2
2/18/2013	41323:10	L-7535	10	2/21/2013	41326:3	3
2/18/2013	41323:11	T-7536	11	2/21/2013	41326:4	4
2/18/2013	41323:12	K-7537	12	2/21/2013	41326:5	5
2/18/2013	41323:13	A-1615	13	2/21/2013	41326:6	6
2/18/2013	41323:14	M-7538	14	2/21/2013	41326:7	7
			15
			16
2/19/2013	41324:1	K-7539	1	2/21/2013	41326:8	8
2/19/2013	41324:2	F-7540	2	2/21/2013	41326:9	9
2/19/2013	41324:3	B-1616	3	2/21/2013	41326:10	10
2/19/2013	41324:4	S-7541	4	2/21/2013	41326:11	11
2/19/2013	41324:5	K-7542	5	2/21/2013	41326:12	12
2/19/2013	41324:6	S-7543	6	2/21/2013	41326:13	13
2/19/2013	41324:7	A-1617	7	2/21/2013	41326:14	14
2/19/2013	41324:8	W-7544	8	2/22/2013	41327:1	1
2/19/2013	41324:9	M-7545	9	2/22/2013	41327:2	2
2/19/2013	41324:10	S-7546	10	2/22/2013	41327:3	3
2/19/2013	41324:11	A-1618	11	2/22/2013	41327:4	4
2/19/2013	41324:12	S-7547	12	2/22/2013	41327:5	5
2/19/2013	41324:13	X-7548	13	2/22/2013	41327:6	6
2/19/2013	41324:14	K-7549	14	2/22/2013	41327:7	7
			15
			16
2/20/2013	41325:1	L-7550	1	2/22/2013	41327:8	8
2/20/2013	41325:2	T-7551	2	2/22/2013	41327:9	9
2/20/2013	41325:3	K-7552	3	2/22/2013	41327:10	10
2/20/2013	41325:4	A-1619	4	2/22/2013	41327:11	11
2/20/2013	41325:5	M-7553	5	2/22/2013	41327:12	12
2/20/2013	41325:6	K-7554	6	2/22/2013	41327:13	13
2/20/2013	41325:7	F-7555	7	2/22/2013	41327:14	14
2/20/2013	41325:8	B-1620	8	2/25/2013	41330:1	1
2/20/2013	41325:9	S-7556	9	2/25/2013	41330:2	2
2/20/2013	41325:10	K-7557	10	2/25/2013	41330:3	3
2/20/2013	41325:11	S-7558	11	2/25/2013	41330:4	4
2/20/2013	41325:12	A-1621	12	2/25/2013	41330:5	5
2/20/2013	41325:13	W-7559	13	2/25/2013	41330:6	6
2/20/2013	41325:14	M-7560	14	2/25/2013	41330:7	7
			15
			16
2/21/2013	41326:1	S-7561	1	2/25/2013	41330:8	8
2/21/2013	41326:2	A-1622	2	2/25/2013	41330:9	9
2/21/2013	41326:3	S-7562	3	2/25/2013	41330:10	10
2/21/2013	41326:4	X-7563	4	2/25/2013	41330:11	11
2/21/2013	41326:5	T-7564	5	2/25/2013	41330:12	12
2/21/2013	41326:6	L-7565	6	2/25/2013	41330:13	13
2/21/2013	41326:7	T-7566	7	2/25/2013	41330:14	14
2/21/2013	41326:8	K-7567	8	2/26/2013	41331:1	1
2/21/2013	41326:9	A-1623	9	2/26/2013	41331:2	2
2/21/2013	41326:10	M-7568	10	2/26/2013	41331:3	3
2/21/2013	41326:11	K-7569	11	2/26/2013	41331:4	4
2/21/2013	41326:12	F-7570	12	2/26/2013	41331:5	5
2/21/2013	41326:13	B-1624	13	2/26/2013	41331:6	6
2/21/2013	41326:14	S-7571	14	2/26/2013	41331:7	7
			15
			16
2/22/2013	41327:1	K-7572	1	2/26/2013	41331:8	8
2/22/2013	41327:2	S-7573	2	2/26/2013	41331:9	9
2/22/2013	41327:3	A-1625	3	2/26/2013	41331:10	10
2/22/2013	41327:4	W-7574	4	2/26/2013	41331:11	11
2/22/2013	41327:5	M-7575	5	2/26/2013	41331:12	12
2/22/2013	41327:6	S-7576	6	2/26/2013	41331:13	13
2/22/2013	41327:7	A-1626	7	2/26/2013	41331:14	14
2/22/2013	41327:8	S-7577	8	2/27/2013	41332:1	1
2/22/2013	41327:9	X-7578	9	2/27/2013	41332:2	2
2/22/2013	41327:10	T-7579	10	2/27/2013	41332:3	3
2/22/2013	41327:11	L-7580	11	2/27/2013	41332:4	4
2/22/2013	41327:12	T-7581	12	2/27/2013	41332:5	5
2/22/2013	41327:13	K-7582	13	2/27/2013	41332:6	6
2/22/2013	41327:14	A-1627	14	2/27/2013	41332:7	7
			15
			16
2/25/2013	41330:1	M-7583	0	2/27/2013	41332:8	8
2/25/2013	41330:2	K-7584	0	2/27/2013	41332:9	9
2/25/2013	41330:3	F-7585	0	2/27/2013	41332:10	10
2/25/2013	41330:4	B-1630	0	2/27/2013	41332:11	11
2/25/2013	41330:5	S-7586	0	2/27/2013	41332:12	12
2/25/2013	41330:6	K-7587	0	2/27/2013	41332:13	13
2/25/2013	41330:7	S-7588	0	2/27/2013	41332:14	14
2/25/2013	41330:8	A-1631	0	2/28/2013	41333:1	1
2/25/2013	41330:9	W-7589	0	2/28/2013	41333:2	2
2/25/2013	41330:10	M-7590	0	2/28/2013	41333:3	3
2/25/2013	41330:11	S-7591	0	2/28/2013	41333:4	4
2/25/2013	41330:12	A-1632	0	2/28/2013	41333:5	5
2/25/2013	41330:13	S-7592	13	2/28/2013	41333:6	6
2/25/2013	41330:14	X-7593	14	2/28/2013	41333:7	7
			15
			16
2/26/2013	41331:1	T-7594	1	2/28/2013	41333:8	8
2/26/2013	41331:2	L-7595	2	2/28/2013	41333:9	9
2/26/2013	41331:3	T-7596	3	2/28/2013	41333:10	10
2/26/2013	41331:4	K-7597	4	2/28/2013	41333:11	11
2/26/2013	41331:5	A-1633	5	2/28/2013	41333:12	12
2/26/2013	41331:6	M-7598	6	2/28/2013	41333:13	13
2/26/2013	41331:7	K-7599	7	2/28/2013	41333:14	14

<tbody>
</tbody>

 

[Sancdar]

Do you already know which Fridays you do work and which Mondays you don't work?

 

[GWard02]

it varies, but yes. The data you see in my source is a rowset that changes dynamically based on the production spray schedule (another sheet in the workbook). So as the spray schedule (4 wks) rolls forward, the rows I pasted in advance automatically. So while we know them up to 6 weeks out, it isn't something I could use the workdays function on (we're trying to make this a "code it and forget it" kind of thing if we can).