• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible to help me translate this instruction into a formula?

M

melzjm

New Member
Hi All,

I am just wondering if this is ever possible. :( I have been trying my best to make this as easy as possible for 2 weeks now but the only thing that would work is to manually do it.


I have been tasked to send a report based on the following requirement with only these column. (Reported Date, Closed Date and Details of the Issue). I am trying to avoid doing this manually since itll take me forever , so I am wondering if this requirement can be translated into a formula.


1.)If "Reported Date of issue" is during office hours (M-F, 0800-1700)

then Severity 1= 2hrs to a max of 4hrs SLA when the vendor work gives a work around solution provided that the work around does not require data patching emargency implementation but if it does, then SLA is to complete before the start of the next business day including saturday. If it is a permanent solution SLA is until 3 days.


2) If "Reported Date of issue" is after office hours, then Severity 1 = SLA is 3hrs until 6pm of the next business day when the vendor work gives a work around solution provided that the work around does not require data patching emargency implementation but if it does, then SLA is to complete before the start of the next business day including saturday.


Thanks,

Melzjm
 
https://docs.google.com/spreadsheet/ccc?key=0AsipP_XUVnFwdE45Rl80MkhVXzB1c3Zlb1pBTVZfaEE


Then , I was thinking. If I am able to get the Target Completion Date based on the type of resolution which I have to get somewhere, then I can calculate if this particular task passed the SLA.


Does it sound right? Any inputs will really be appreciated.
 
Hi ,


I think you are attempting too much in one go ! Let us take it one step at a time.


1. You have three dates viz. Reported Date ( column A ) , Resolved Date ( column D ) and Closed Date ( column E ) ; can you clearly indicate which of these will be used to arrive at whatever you want , and how ?


2. You have several different types of resolution ; the sample worksheet contains 4 of them ; are these the only 4 or will there more ? Again , the same question - which of these will be used to arrive at whatever you want , and how ?


From what you have mentioned , are you looking to get a formula for SLA ( column I ) ?


From what you have mentioned in item 1 of your question , let me try and break it down :


Check whether the Reported Date is within office hours ( defined as between 08:00 and 17:00 from Monday through Friday ).


What is the meaning of the following : "provided that the work around does not require data patching emargency implementation" ? The column G ( Type of Resolution ) mentions only one type of resolution ; if a vendor provides a work around which requires Data Patching and / or Emergency Implementation , how will all of these be indicated ?


In some cases you have mentioned an SLA in hours , elsewhere you mention 3 days ; does 3 days equate to 72 hours ?


I think the more we get down to specifying the nitty-gritty , the easier it will be to get a robust solution.


Narayan
 
You must log in or register to reply here.
Back
Top