Formula to calculate SLA by excluding non-business hours, weekends and holidays

Hi Everyone,

I have a data of Incidents with Start date with time, End time with time.

Start time: 7/1/13 10:30 AM in C2 cell

End time: 7/9/13 5:33 PM in D2 cell

Formula which i was using to calculate SLA was =(NETWORKDAYS(C2,D2)-1)*("18:00"-"08:00")+IF(NETWORKDAYS(C2,D2),MEDIAN(MOD(D2,1),"18:00","08:00"),"18:00")-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),"18:00","08:00")

The TTR is 67:03:07

Now the above formula excludes just the week ends and non-business hours.

But i need to exclude the bank holidays as well.

Business hours are 08:00:00 to 18:00:00.

Kindly help me towards Resolution.

Regards
Kokila.S

Hi
Have a look at this link which gives you a solution:
http://www.mrexcel.com/forum/excel-q...-weekends.html
Good luck.
Tony

Hi Tony,

Thank you very much for your help. will check and update the status shorlty.

Regards
Kokila.S

Hi Audere,

Thank you very much for your help. you have made my day.

its working completely fine.

Regards
Kokila.S

Hi daddylonglegs

This formula works perfectly, but can you please help me to understand what is it doing?
I have to explain this to someone but to be honest, I have no idea what it is doing.

Please help, thanks!

Hi Kokila,

Please can you share me the Sample spreadsheet.

Regards, Sama

Hi Guys,

I tried the above formula of "=(NETWORKDAYS(C2,D2,H$2:H$10)-1)*("18:00"-"08:00")+IF(NETWORKDAYS(D2,D2,H$2:H$10),MEDIAN(MOD(D2,1),"18:00","08:00"),"18:00")-MEDIAN(NETWORKDAYS(C2,C2,H$2:H$10)*MOD(C2,1),"18:00","08:00")"

But it gives me 01/01/1900 21:00...how do i get the total time in hh:mm:ss.....

If i convert the above result it only gives me 21:00:00.


Thanks,
Vicky

Format Cells -> Custom -> [h]:mm