Calculate delivery performance with delivery +/- 24 h from planned date/time excluding non

Hello,

I’m writing a script for a delivery performance where I want to compare the planned date and time of delivery with the actual delivery date and time.
If the delivery occurred as planned, the performance is 100 %, but if the delivery occurred within a given time span eg. +/- 24 h, the delivery is calculated linear. I.e. if the delivery is 24 h early or late the performance is 0 %, and if the delivery is 12 h early or late the performance is 50 %.

It gets a bit more complicated since I want to exclude weekends and non-working hours (workday is between 0600-22.15) from the result.

Can anybody help me?

Thanks for your help!

Best regards
Erik

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Thank you arthurbr!

I’m not sure that I’m following you all the way. Is it possible to try to explain it a bit more step by step?


Best regards Erik

My specifications:
Windows 7 Home Premium SP1 64 bit
Microsoft office Professional Plus 2010 32 bit