SLA calculation

Hi
Thanks for taking the trouble to read this.
My question is that I need to calculate how long a delivery took to arrive at its destination. However it depends on when the order was placed. If the order was placed today before 5pm, I would use today as day 1. However if the order was placed today but after 5pm, I count tomorrow as day 1.
I'd thenn subtract the delivery date from the calculated day 1 date. I also have to ignore weekends and public holidays. I tried using NETWORKDAYS and a table of holiday date to reference, but I failed. I'm sure its simple. Can anyone help?

Thanks in advance.

Try this:

Formula:

Please Login or Register  to view this content.

Zbor, I knew it would be simple! Many thanks!

Now I have to figure out how to include the holiday table. I might be back.

Thanks again, Zbor

Define list of holidays on some other sheet and just select range as a third argument.
don't forget to lock range with $

Define list of holidays on some other sheet and just select range as a third argument.
don't forget to lock range with $

Zbor, I knew it would be simple! Many thanks!

Now I have to figure out how to include the holiday table. I might be back.

Thanks again, Zbor

I knew I'd be back!

When I introduce the holidays argument as suggested, the days are still counted. However, when I change the time to before or after "13:00", the count doesn't change accordingly?

=NETWORKDAYS(A1-"13:00", B1,Sheet2!$L1:$L98) -1

@ jjaggii,

You can try this. Should work.

For Excel 2003

Please Login or Register  to view this content.

For Excel 2007 or 2010

Please Login or Register  to view this content.

Assuming that your start date and end date contains date and time. So extracting time from start date and checking if order placed before 1700 or after 1700 and accordingly calculating the number of days. You can list your holidays accordingly and modify the formula. Let me know if this is what you were looking for.

Hi Dll

A1 contains 07/05/2013 14:05
B1 contains 28/05/2013
Sheet2!$L1:$L98 contains a list of annual holidays(including school holidays)
There are two public holiday in the date range that Ive specified
So, when I exclude the holiday from the formula like this,

=NETWORKDAYS(B9-"13:00", C9) -1

I get an answer of 15. If I move the time from 14:05 to 12:05 my answer is 16. This is what I want. However when include the holiday as another argument as i'm my previous post the answer doesnt change when I change the time from 14:05 to 12:05 or vice versa.

Hi arindam2013 and thanks for replying. Like Zbor above you have wonderful skills with Excel. Your formula worked a treat. Many thanks for your help!

Good to know that I was able to solve your problem. Above you stated that
A1 contains 07/05/2013 14:05
B1 contains 28/05/2013
This would provide you with incorrect result, since B1 doesnt have any time mentioned. Excel will by default take the time as 00:00. Still it doesnt seem to a major issue now. But just remember incase you come across with any issue.

Thanks for that Dll. Ive briefly tried your update formula and results have been consistent with the existing formula, including a Monday holiday. I'll run both in parallel for a while to monitor. Many thanks.