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