Remove non-working hours from SLA formula

[Mlambe87]

Good morning,
I am a somewhat of a novice when it comes to excel and especially formulas so I hope someone can help me without having to go to the 'go to guy' in the office!
My issue is, we have to track our work manually and have a time & date in column and a time & date picked up column. The format is dd/mm/yyyy hh:mm for both (UK) and I need to know how long each take to pick up without including non-working hours. Our working hours are Mon-Sat 08:00-20:00 and our SLA is 24hrs the next business day.
Thanks in advance for your help!

[Ursul]

Not sure of I've understood correctly but I've created a very simple example and maybe we can start with this. Cell D2 is the cell with the simple formula in it. Note because we are working with time "*24" is in the formula or else you just get a General number which looks crazy!

Time Worked.xlsx

Cheers

[Ursul]

Sorry forgot to ask; what does "our SLA is 24hrs the next business day" mean?

[Phenarole]

Hi Ursul,

Your solution works great for work turned over on the same day but, in reality, there will be times when the standard 24 hour turn will not happen and the daily non-productive hours will need to be subracted from the total hours.

I added to your base formula to create a solution that takes the unusual circumstances that would compensate for the 10 hours each day the facility is not operating.

The "closed" hours are determined by total hours in house divided by 24. This leaves a whole number with a decimal fraction that is used in a VLOOKUP for the hours to subtract from the total hours. The table can be increased in size depending on the user's real world worst case.

[Phenarole]

In my fixation with the over 24 hour turn I failed to test for same-day completion. Not a huge thing but, nevertheless...

This is the corrected formula that will work for both instances (second part of IF statement is now in parentheses).

Please Login or Register  to view this content.

Sorry for any confusion.