Excel IF critreria

Question

I have a request where I am trying to determine if we hit our metrics with tickets opened to our company. Essentially we have to respond to cases within by the next business day, the exception here is if the ticket is opened after Friday 18:00 we get to wait till Tuesday end of day 18:00

So my original thinking was if a ticket: 1) a ticket is not opened, Fri, Sat, Sun then if response time is less than 24 then we MET 2) a ticket is opened up after 18:00 Friday then we have till EOD Tuesday 18:00 - it does not matter what time within Friday 18:00 to Sunday 23:59. 3) If a ticket is opened up Friday 00:00-117:59 then we have until Monday 18:00

Problem is I don't know how to calculate what the time should be, in a perfect situation a case is opened on Friday 17:00 and we have exactly 72 hours to respond. But cases are opened anytime throughout the weekend and how do I subtract that from the 72 to determine if we met or not.

Here is an example of my table

Month   Year    Day Time    Hours   Met/Miss
January 2017    Wed 15:20   19.77   
January 2017    Tue 9:02    2.04    
January 2017    Tue 11:35   0       
January 2017    Fri 10:37   0.19    
January 2017    Fri 17:48   89.06   
January 2017    Mon 0:33    0.03

Any suggestions, I am also going to wrap this into VBA so that a macro does all this work.

Should know that I am only a beginner-intermediate in Excel and VBA - sorry

Answer 1

You're going to be better off if you can input your ticket open time as a date, then use a VBA function to calculate a due date. Then you can either calculate a ticket closed date and compare, or calculate a time difference and compare. Hypotheically you don't actually need your day of month because you only care about day of week, but it would be best to include day of month as shown below.

Edit The same functionality of the VBA function below can be achieved by the worksheet equation:

=IF(WORKDAY(WORKDAY(H2,1,Holidays),-1,Holidays) = ROUNDDOWN(H2,0), WORKDAY(H2,IF(HOUR(H2)>=18,2,1),Holidays)+18/24, WORKDAY(WORKDAY(H2,-1,Holidays),2,Holidays) + 18/24)

Where the H column has ticket opened dates and Hol is your list of holidays.

It may or may not be easier/better to use this VBA function which basically does the same thing without holidays. The VBA function can be friendlier to future programming.

Function CalculateDueTime(OpenedTime As Date)
    Dim DueTime As Date
    Dim wkd As Integer
    wkd = Weekday(OpenedTime, vbSunday)
    If wkd = 1 Then
        'Zero the hours, Add 2 days to get from Sunday to Tuesday, then get to 18:00
        DueTime = DateAdd("h", 18, DateAdd("d", 2, DateAdd("h", -Hour(OpenedTime), OpenedTime)))
    ElseIf wkd = 7 Then
        'Zero the hours, Add 3 days to get from Saturday to Tuesday, then get to 18:00
        DueTime = DateAdd("h", 18, DateAdd("d", 3, DateAdd("h", -Hour(OpenedTime), OpenedTime)))
    Else
        'Add an hours portion to see what day we arrive at
        'The reason this works is because what you actually have is an offset in your end-of-day from midnight to EOB.
        'So we're accounting for that offset by adding 6 hours then adding 24 to get to our due day,
        'then defining 18:00 as our due time on that due day.
        DueTime = DateAdd("h", 6 + 24, OpenedTime)
        'Whatever day we arrived at above, zero the hours and add 18
        DueTime = DateAdd("h", 18, DateAdd("h", -Hour(DueTime), DueTime))

        wkd = Weekday(DueTime, vbSunday)
        If wkd = 7 Or wkd = 1 Then
            'If our due date lands on the weekend
            'we can always resolve it by adding 2 days
                'Opened Thur due Sat becomes due Mon
                'Opened Friday due Sat becomes due Mon
                'Opened Friday due Sunday becomes due Tues
            DueTime = DateAdd("d", 2, DueTime)
        End If
    End If
    CalculateDueTime = DueTime
End Function

This gives the the following results...

It doesn't really matter, but if you're curious about the date formatting here is the custom format I used.