# Calculate time difference and convert

1. ## Calculate time difference and convert

Hello All,

I run the following code on a macro and need help making some changes.
When the macro runs the columns are already se to auto filter.
Column I is created by the macro and names it SLA, time here is given in total hours.
The macro looks at column E and filters on Medium, high and top, date and time is on column H if Medium is > 120 color background on column I RED, if High is > 72 color background on column I RED, if top is > 4 color background on I RED.
I would like to add if Medium is > 96 but < 120 color background on I yellow, if High is > 60 but <72 color background on I yellow, if Top is > 3 but < 4 color background on I yellow.
The time calculated is given in decimal hours, which can run in the hundreds, is there a way to convert this back to it's corresponding date and time?

Also after all the calculations and coloring is done, is it posible to look at column K and filter by the following keywords TEAM "A", TEAM "B", TEAM "DBA", and SDSK.
Look at column I and count how many REDs and how many YELLOWS for each of the above keywords out of the total for each on K column, and put this count on any columns after Column, which are not used, for example:

TEAM A - 73 Tickets out of which 23 are in RED AND 15 ARE YELLOW

TEAM B - 52 Tickets out of which 14 are in RED AND 5 ARE YELLOW

TEAM DBA - 12 Tickets out of which 0 are in RED AND 5 ARE YELLOW

TEAM SDSK - 4 Tickets out of which 1 are in RED AND 0 ARE YELLOW

your help is greatly appreciated, following is the code used.

Dim ws1 As Worksheet
Dim r As Long, lastrow As Long

Set ws1 = Worksheets("Currently Open Report")
With ws1

Columns("I").Insert Shift:=xlToRight
.Cells(1, "I") = "Deadline SLA"
Columns("I").NumberFormat = "###0.00"

lastrow = .Cells(Rows.Count, "E").End(xlUp).Row

dt2 = CDec(Now())

For r = 2 To lastrow

dt1 = CDec(.Cells(r, "H"))

Select Case UCase(.Cells(r, "E"))

Case Is = "MEDIUM"
TimeDiff = (networkdays(dt1, dt2) + ((dt2 - Int(dt2)) - (dt1 - Int(dt1)))) * 24
.Cells(r, "I") = TimeDiff
If TimeDiff > 120 Then
.Cells(r, "I").Interior.ColorIndex = 3
End If

Case Is = "HIGH"
TimeDiff = (networkdays(dt1, dt2) + ((dt2 - Int(dt2)) - (dt1 - Int(dt1)))) * 24
.Cells(r, "I") = TimeDiff
If TimeDiff > 72 Then
.Cells(r, "I").Interior.ColorIndex = 3
End If

Case Is = "TOP"
TimeDiff = (dt2 - dt1) * 24
.Cells(r, "I") = TimeDiff
If TimeDiff > 4 Then
.Cells(r, "I").Interior.ColorIndex = 3
End If
End Select
Next r

End With

End Sub  Register To Reply

2. Hello all,

Can any body help with the issue below, or point me in the right direction, txs.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1