Good morning everyone. I'm measuring two wait periods for a delivery cycle in days i.e., Wait 1 = the number of days between the order date and the ready to ship date, and Wait 2 = the number of days between the ready to ship date and delivery date. Within each wait period, we can have multiple date ranges where delivery is unavailable, and should be subtracted from the overall wait time. The table below outlines two sample records and the expected wait times. To note in the calculation, if there is overlap of an unavailable date with the ready to ship date, it isn't counted in Wait 1, it is counted in Wait 2.
I'm looking for help with either a formula, UDF, or macros that will complete these calculations for thousands of records. Any assistance is hugely appreciated - thank you!
Order Date | Ready to Ship Date | Delivery Date | Delay 1 Start Date | Delay 1 End Date | Delay 2 Start Date | Delay 2 End Date | Delay 3 Start Date | Delay 3 End Date | Wait 1 | Wait 2 |
2019-09-01 | 2019-09-10 | 2019-09-30 | 2019-09-03 | 2019-09-30 | 2019-09-08 | 2019-09-14 | 2019-09-22 | 2019-09-23 | 6 | 13 |
2019-09-06 | 2019-09-09 | 2019-09-19 | 2019-09-08 | 2019-09-11 | 2019-09-18 | 2019-09-18 | 2 | 6 |
I'm looking for help with either a formula, UDF, or macros that will complete these calculations for thousands of records. Any assistance is hugely appreciated - thank you!