Calculate Resolution date and time based on priority SLA

• Hi,

I need to find the resolution date and time based on priority from the reported date of the incident.

Working hours is from 7 AM to 7 PM weekdays, excluding weekends.

SLA timelines based on priority:
2-High - 8 hours
3-Medium - 48 hours(4 days)
4-Low - 240 hours(20 days)

For eg:- If the incident is reported at 6:30 pm on a friday, the resolution date should move to monday along with appropriate time and date.

Thanks

Files

• Re: Calculate Resolution date and time based on priority SLA

thanks for the response Herbds7. But I want the reolution date and time be always as per the SLA timelines given below.

also is there a easier way using WORKDAY and IF commands. thanks.

SLA timelines based on priority:
2-High - 8 hours
3-Medium - 48 hours(4 days)
4-Low - 240 hours(20 days)

• Re: Calculate Resolution date and time based on priority SLA

Quote

...I want the resolution date and time be per...

Edit the tables as desired.

Quote

....is there an easier way...

There are monster formulas, macros and PowerPivot, none easier to customize or to maintain.

• Re: Calculate Resolution date and time based on priority SLA

ok thanks Herbd7.

But I am not getting the desired result.

For eg:- row 7 in your xls
Open date is: Friday 31/08/2012 6:01:29 PM and priority is 2.
As per my requirement the answer should be Monday 02/08/2012 14:01. As the next workday is monday and the time to resolve is 8 hrs. Hence the answer should be 02/08/2102 14:01, as the monday start time is 7 AM. The clock should start from 7 AM from monday and 1 hour on Friday till 19:00 hrs. The working time is 7 AM - 7 PM.

Thanks

• Re: Calculate Resolution date and time based on priority SLA

Sorry the answer should be Monday 02/09/2102 14:01.

• Re: Calculate Resolution date and time based on priority SLA

Quote

Sorry the answer should be Monday 02/09/2102 14:01.

My calendar does not cover 90 years. Sept 2, 2012 is a Sunday.
All my dates are in US format (mm/dd/yy)
Monday, Sept 3, 2012 is a US holiday (Labor Day). See holiday sheet.
You did not edit Table3 for your priority hours and working hours.
My work needed no corrections.

• Re: Calculate Resolution date and time based on priority SLA

Thanks a lot Herbds7, it works perfectly now. Thanks for the detailed explanation as well on the latest xls shared.

I have another question, how do I read the priority and open date from one xls and update? Also I need to change color of the Close date cell, if the date is nearing the close date.

Thanks once again...Can you please tell me how the Fin() works in the xls.

• Re: Calculate Resolution date and time based on priority SLA

Added CF to color "Close" date.
To update, copy and paste date/time from another *.xlsx.
There isn't enough space here to explain Fin()

• Re: Calculate Resolution date and time based on priority SLA

Thanks Herbsd7. I need one more help from you.

Please help me find the resolution date and response dates for service requests and incidents. I have put the response SLA and other details required in the attached xls.

Thanks and Regards,
Girish

Files

• Re: Calculate Resolution date and time based on priority SLA

Thanks for all the help Herbds7, you are too good!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!