Help - Calculate SLA For Between Two Time Difference

Dear Excel Gurus,

I need your help with a formulas to calculate SLA time between created date - resolved date.

Here are the basic conditions to calculating between 2 dates and times with the following condition:
- Working hours: 08:00 AM to 17:00 PM
- Exclude for Lunch time: 12.00PM to 13.00PM
- Exclude Weekends.
- Exclude Public holiday (Holiday list)
- Engineer need to change status as Resolved.
- Status resolved will changed to closed after 24hrs automatically executed by system
- If the time elapsed exceeds 24 hours, use the d �days� h:mm time format (exp: 6 days 5:23)

Thank you for the help as always.

Cheers
Fry

What is SLA?

How is D2=8.44. Pl explain.

How it is 12,44 . Pl explain in detail.
This is what I feel.
On 11/2 it is 8:48
On 12/2 it is 6:56
Total 15:44

Please try in D2 and copy down:

Formula:

Please Login or Register  to view this content.

Dear HansDouwe,
Thank you for your help on this and I will do a simulation with your formula. Thanks!

Dear josephteh,
Thank you for your help on this and need to learn slowly for your formula. Thanks!

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.