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

    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.


    Also can you please explain your xls :).


    Thanks

    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.
    I highlighted all your mistakes.
    My work needed no corrections.
    Re-download link.

    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-download link.

Participate now!

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

Register Yourself Login