need function to find the SLA between days and hours excluding non business hours

Hi Team,

I have tried using the formulas from other posts but it is not working, since I am getting results as 00:00:00. I need to calculate SLA or TAT by excluding non business hours. Kindly help me. attached sample file for your reference. Refer column "O" "P" and "R".

working hours: 07 AM to 8 PM.
working days - Monday to Friday.

Perhaps if you gave some expected answers, we would be able to help you.

https://exceljet.net/formula/get-wor...ates-and-times gives you the method, but given all your times are outside of your working hours the hours with your working hours are 0. It may be the formula you are using is correct!


Approved At Ordered
29/04/2021 22:07 29/04/2021 22:42


hours within working hours are 0! and the same for every other row!

@davsth : Thank you very much for checking.

if it is approved and ordered date are outside of working hours. I need to include the formula in the same formula string to calculate ordered - approved at hours. Kindly help me.

For example here I need the difference between below given timings.

Approved At Ordered
29/04/2021 22:07 29/04/2021 22:42

You need to give expected answers for various scenarios. You asked to exclude non business hours originally!

What would
29/04/2021 22:07 30/04/2021 22:42 be?
29/04/2021 6:00 29/04/2021 8:00 be?
29/04/2021 16:00 29/04/2021 22:00 be?
29/04/2021 22:07 29/04/2021 22:42 is 0:35?


It is hard to write a formula if we do not know what the answers are

Below are my answers.

29/04/2021 22:07 30/04/2021 22:42 be? - 13 hrs (since it should calculate the business hours for the 30/04/2021)
29/04/2021 6:00 29/04/2021 8:00 be? - 1 hr (since 6:00 to 07:00 is non business hours and the business hours starts from 07:00)
29/04/2021 16:00 29/04/2021 22:00 be? - 4hrs (since post 20:00 is non business hours calculated only from 16:00 to 20:00)
29/04/2021 22:07 29/04/2021 22:42 is 0:35? - yes, if either of the column falls under business hours then the hours should calculate excluding non business hours and if both the columns falls under non business hours, just calculate the difference between both the columns.

Also, I have noticed that the Row 2 in the attached file is having the date and time under business hours but it is still showing 00:00:00.

The first of may was a saturday so 0 is the correct answer!
day so 0 is the correct answer
try(if(yourformula=0,p2-o2,yourformula)

Thank you very much for your suggestions.

I did tried the way you have given but I am still getting 0 values for the below.

5/1/2021 14:30 5/1/2021 15:43 (getting only ##### symbols)
5/3/2021 23:44 5/4/2021 0:34 (getting 0 value)
5/3/2021 23:01 5/4/2021 1:34 (getting 0 value)
Kindly check and assist.

@davsth - Kindly help me on the request above.

Did you even make the change I suggested in post 6? it works with the last 2 the ### are as a rounding error

try if(round(yourformula,6)=0,p2-o2,yourformula)

Please Login or Register  to view this content.

@davsth: Thank you very much, it works as per my requirement.