Figuring out Time lapsed, but accounting for core / non core hours???

[robjones23]

Hi,

I'm trying to figure out how to get this working and i'm struggling to get my head around it. I need to create a Database which looks at outages in a system and calculates the outage time.

Initially this seemed easy enough and I soon had a database which would figure out hrs / mins of an outage. The only problem is that I now need to account for supported hours.

Our hours of support are 07:00:01 - 21:59:59. Anything outside of this would be considered "out of hours".

So if a system falls over at 8PM, and then doesn't come back up until 8AM the next day, even though the real time hours would = 12 hours, the actual reported hours needs to be 3 hours.

I'd need 3 outputs, outage total (which I already have), outage core (between 07:00:01 - 21:59:59) and outage non core (which could probably just be total - core if thats easier)

Any ideas on the best way to accomplish this? I'd like to keep it as efficient as possible but the data coming in may hinder this (It needs some formatting to get the correct dd/mm/yyy hh:mm:ss formatting.

Cheers,

Rob.

 

[RichO]

This was an intriguing one. The best way I could come up with is a slow moving function. What it does is start at the OutTime and count by seconds until it reaches the InTime, only adding the seconds that fall between 7:00 AM and 10:00 PM. If accuracy to the second is not important to you, you can greatly speed up the function by changing it to minutes. Simply change the "s" to "n" in the 2 DateAdd functions.

Public Function Outage(SysOff As Date, SysOn As Date) As Date
    Dim diff As Date
    Dim hrs1 As Date
    Dim hrs2 As Date
        
    Do While SysOff < SysOn
        hrs1 = CDate(Format$(SysOff, "mm/dd/yyyy") & " " & "07:00:00 am")
        hrs2 = CDate(Format$(SysOff, "mm/dd/yyyy") & " " & "10:00:00 pm")
        SysOff = DateAdd("s", 1, SysOff)
        If SysOff > hrs1 And SysOff < hrs2 Then
            diff = DateAdd("s", 1, diff)
        End If
    Loop
    Outage = diff
End Function

 

[robjones23]

That works great! Thanks so much, only thing I had to do was change mm/dd/yyyy to dd/mm/yyyy

I may need to account for some other times on specific dates (someone just told me we have no coverage on sundays and only 08:00 - 12:00 [midday] on a saturday)

I think I have the buliding blocks to incorporate this, I'm guessing I could add something to set that if the date is monday - friday then do the above script etc...

 

[robjones23]

Hmmm... well it DID work great

I've got a problem - when the diff is more than 1 day, I get things like 01/01/1899 04:00:05 etc....

Can it be formatted to say XXdays XXhours XX mins ????

 

[robjones23]

Scratch that, I used the following on the resulting field to calculate hours:minutes

Public Function HoursAndMinutes(interval As Variant) As String
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440))   ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400))  ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1  ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

 

[robjones23]

I also got the other time figured out too (non-core times). I needed 4 hrs intervals to set the non core before the core hours.

I.e. 00:00:00 am - 07:00:00
and 22:00:01 pm - 23:59:59

used this:

Public Function Downtime(systemOff As Date, SystemOn As Date) As Date
    Dim diff2 As Date
    Dim diff As Date
    Dim hrs1 As Date
    Dim hrs2 As Date
    Dim hrs3 As Date
    Dim hrs4 As Date
    
    If IsNull(Downtime) = True Then Exit Function
         
    Do While systemOff < SystemOn
        hrs1 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "07:00:00 am")
        hrs2 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "00:00:00 am")
        hrs3 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "23:59:59 pm")
        hrs4 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "22:00:01 pm")
        systemOff = DateAdd("n", 1, systemOff)
        If systemOff < hrs1 And systemOff > hrs2 Then
            diff = DateAdd("n", 1, diff)
        End If
        If systemOff < hrs3 And systemOff > hrs4 Then
            diff2 = DateAdd("n", 1, diff2)
        End If
    Loop
    Downtime = diff + diff2
End Function

 

[robjones23]

Sorry to resurrect this thread but I've got a new requirement which I'm not sure how to incorporate (if possible!!)

At the mo I'm using different code to the above as things changed as we learnt what was required.

Currently I'm using:

Public Function Downtime(systemOff As Date, SystemOn As Date, sev As Integer) As Date
    Dim diff2 As Date
    Dim diff As Date
    Dim hrs1 As Date
    Dim hrs2 As Date
    Dim hrs3 As Date
    Dim hrs4 As Date
    
 If IsNull(systemOff) = True Then Exit Function
 
    If SystemOn = "31/12/9999" = True Then
    SystemOn = systemOff
    End If
        
  
    
 If sev > 3 Then
         
    Do While systemOff < SystemOn
        hrs1 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "07:00:01 am")
        hrs2 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "09:59:59 pm")
        systemOff = DateAdd("s", 1, systemOff)
        If systemOff > hrs1 And systemOff < hrs2 Then
            diff = DateAdd("s", 1, diff)
        End If
    Loop
      Downtime = diff
End If
    
If sev <= 3 Then

Do While systemOff < SystemOn
        hrs1 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "23:59:59 pm")
        hrs2 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "00:00:00 am")
        systemOff = DateAdd("s", 1, systemOff)
        If systemOff < hrs1 And systemOff > hrs2 Then
            diff = DateAdd("s", 1, diff)
            End If
    Loop
        Downtime = diff
    End If


End Function

What I need is to account for newly negotiated weekend hours where we can stop a clock. So rather than 07:00 - 22:00 for all sev 4 / 5 and 24 hours for sev 1,2,3 we now additionally have that anything after 17:30PM on a friday up to 08:30 on a monday is classed as out of hours so is not included in the calculation.

so if a call was logged on saturday at 8AM and resolved on monday at 9AM the fix time would be reported as 30 minutes.

Anyone got any ideas?? I was thinking I could use the "Weekday" function??

 

[RichO]

This is the idea I came up with using Select Case to define the core times based on the day of the week. As you see, Sat & Sun will return 0 seconds of core time. I did not test this code so please forgive if it fails. Just one one idea of how to do this...

    Dim tyme1 As String
    Dim tyme2 As String
    Dim diff As Date
    Dim hrs1 As Date
    Dim hrs2 As Date
    Do While systemOff < SystemOn
        Select Case DatePart("w", SystemOff)
            Case 1, 7       'Sun & Sat
                tyme1 = "12:00:00 am"
                tyme2 = "12:00:00 am"
            Case 2          'Mon
                tyme1 = "08:30:00 am"
                tyme2 = "10:00:00 pm"
            Case 6          'Fri
                tyme1 = "07:00:00 am"
                tyme2 = "05:30:00 pm"
            Case 3, 4, 5    'Tue, Wed, Thu
                tyme1 = "07:00:00 am"
                tyme2 = "10:00:00 pm"
        End Select
        hrs1 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & tyme1)
        hrs2 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & tyme2)
        systemOff = DateAdd("s", 1, systemOff)
        If systemOff > hrs1 And systemOff < hrs2 Then
          diff = DateAdd("s", 1, diff)
        End If
    Loop

 

[robjones23]

Sorry for the late reply I went on holiday

That worked great thanks again!

Code looks like this:

Public Function Downtime(systemOff As Date, SystemOn As Date, sev As Integer) As Date
    Dim diff2 As Date
    Dim diff As Date
    Dim hrs1 As Date
    Dim hrs2 As Date
    Dim tyme1 As String
    Dim tyme2 As String
    
 If IsNull(systemOff) = True Then Exit Function
 
    If SystemOn = "31/12/9999" = True Then
    SystemOn = systemOff
    End If
        
  
    
 If sev >= 3 Then
         
    Do While systemOff < SystemOn
        Select Case DatePart("w", systemOff)
            Case 1, 7       'Sun & Sat
                tyme1 = "12:00:00 am"
                tyme2 = "12:00:00 am"
            Case 2, 3, 4, 5, 6      'Weekdays
                tyme1 = "08:30:00 am"
                tyme2 = "17:30:00 pm"
        End Select
        hrs1 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & tyme1)
        hrs2 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & tyme2)
        systemOff = DateAdd("s", 1, systemOff)
        If systemOff > hrs1 And systemOff < hrs2 Then
          diff = DateAdd("s", 1, diff)
        End If
    Loop
      Downtime = diff
End If
    
If sev < 3 Then
    Do While systemOff < SystemOn
        hrs1 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "23:59:59 pm")
        hrs2 = CDate(Format$(systemOff, "dd/mm/yyyy") & " " & "00:00:00 am")
        systemOff = DateAdd("s", 1, systemOff)
        If systemOff < hrs1 And systemOff > hrs2 Then
            diff = DateAdd("s", 1, diff)
            End If
    Loop
        Downtime = diff
    End If


End Function

I had to change the times slightly (I guess i didn't explain correctly that it was now blanket mon - friday 8:30 am - 17:30 pm support times)

Thanks again Rich!