Calculate absolute time difference between two dates and times while excluding weekends and non-working hours.

Hi,

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.

It gets a bit complicated since I want to exclude weekends and non-working hours (workday is between 0600-22.15) from the result and I want an early delivery to be weighted the same as a late delivery, given that the absolute difference from the planned date and time is the same.

Can someone help me with a code which calculates the absolute difference between two dates and times which excludes non-working hours and weekends in the way the attached picture suggests.

Thanks for your help!

Best regards
Erik

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

Example

MS project

Hello Erik,

You really need to do it with Excel, because for this kind of purpose I think you could do it easily on MS project. what do you think?

Anyway I will try to find a way to do it on Excel.

Catia Santos