Offsetting an order to a despatch day depending on processing days

[Simba75]

Hi, I have been trying so hard to get this right but am failing terribly.... so I am hoping someone here can help me out.

I need a macro/function that will offset an order amount into a despatch day which is dictated by how many days the order takes to process. I have attached a screenshot as an example. (Using excel 2016)

Example 1: 01/05/20 has an order of 128 units (B5) and it will take 1 day to process (B6). So 1 day after 01/05/20 is 02/05/20 and it is an active despatch day (C4) therefore the order of 128units from 01/05/20 despatches 02/05/20.
Example 2: 02/05/20 has an order of 134 units (C5) and it will take 1 day to process (C6). So 1 day after 02/05/20 is 03/05/20 BUT it is not an active despatch day so it despatches on the next available active despatch day which is 04/05/20.
Example 3: 03/05/20 has an order of 102 units (D5) and it will take 1 day to process (D6). So 1 day after 03/05/20 is 04/05/20 and it is an active despatch day so it despatches 04/05/20 as well as 134 units from 02/05/20
Example 4: 05/05/20 has an order of 101 units (F5) and it will take 6 days to process (F6). So 6 active despatch days in the future is 13/05/20 (don't count the non-despatch days of 06/05 & 10/05), therefore it despatches on 13/05/20.

I think that covers all of the rules - and I am certain I lost my sanity whilst trying to figure this problem out... please, I hope someone here can help me out with this

 

[jasonb75]

Will this do?

The List.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Date	01/05/2020	02/05/2020	03/05/2020	04/05/2020	05/05/2020	06/05/2020	07/05/2020	08/05/2020	09/05/2020	10/05/2020	11/05/2020	12/05/2020	13/05/2020
3	Day	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
4	Despatch Day	Y	Y	N	Y	Y	N	Y	Y	Y	N	Y	Y	Y
5	Order	128	134	102	132	101	100	105	143	103	114	118	135	145
6	Processing Days	1	1	1	1	6	2	1	0	1	1	2	1	0
7	Despatch Date	02/05/2020	04/05/2020	04/05/2020	05/05/2020	13/05/2020	08/05/2020	08/05/2020	08/05/2020	11/05/2020	11/05/2020	13/05/2020	13/05/2020	13/05/2020
8	Despatch1		128		134	132			100			103		101
9	Despatch2				102				105			114		118
10	Despatch3								143					135
11	Despatch4													145
12	Despatch Total	0	128	0	236	132	0	0	348	0	0	217	0	499
Sheet2

Cell Formulas
Range	Formula
B7:N7	B7	=WORKDAY.INTL(B2,B6,"0000001",IF($B$4:$N$4="N",$B$2:$N$2,0))
B8:N11	B8	=IF(B$4="N","",IFERROR(INDEX($5:$5,AGGREGATE(15,6,COLUMN($B8:B8)/($B$7:B$7=B$2),ROWS(B$8:B8))),""))
B12:N12	B12	=SUM(B8:B11)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Simba75]

I'm amazed Jason.... so much thanks for this. It all works perfectly except for 1 thing - when I change the despatch days in row 4 to all "Y" the 3rd & 10th May still act as non despatch days.... but 6th May adjusts fine. Very strange??

 

[Simba75]

Found out why, it was the Workday.intl coding "0000001", changed it to "0000000" and it works like a dream.... thank you so much for figuring this out for me Jason, much appreciated!