Offsetting an order to a despatch day depending on processing days

Simba75

New Member
Joined
Jan 22, 2012
Messages
7
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 :)


Offset order3.PNG
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows
Will this do?
The List.xlsx
ABCDEFGHIJKLMN
2Date01/05/202002/05/202003/05/202004/05/202005/05/202006/05/202007/05/202008/05/202009/05/202010/05/202011/05/202012/05/202013/05/2020
3DayFriSatSunMonTueWedThuFriSatSunMonTueWed
4Despatch DayYYNYYNYYYNYYY
5Order128134102132101100105143103114118135145
6Processing Days1111621011210
7Despatch Date02/05/202004/05/202004/05/202005/05/202013/05/202008/05/202008/05/202008/05/202011/05/202011/05/202013/05/202013/05/202013/05/2020
8Despatch1 128 134132  100  103 101
9Despatch2   102   105  114 118
10Despatch3       143    135
11Despatch4            145
12Despatch Total0128023613200348002170499
Sheet2
Cell Formulas
RangeFormula
B7:N7B7=WORKDAY.INTL(B2,B6,"0000001",IF($B$4:$N$4="N",$B$2:$N$2,0))
B8:N11B8=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:N12B12=SUM(B8:B11)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Simba75

New Member
Joined
Jan 22, 2012
Messages
7
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

New Member
Joined
Jan 22, 2012
Messages
7
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,471
Messages
5,596,347
Members
414,060
Latest member
hermanseck

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
Top