ExcelBanter - 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

--
chin_un_len
------------------------------------------------------------------------
chin_un_len's Profile: http://www.excelforum.com/member.php...o&userid=30914
View this thread: http://www.excelforum.com/showthread...hreadid=508812