+ Reply to Thread
Results 1 to 2 of 2

Calculate time difference and convert

  1. #1
    Registered User
    Join Date
    01-27-2006
    Posts
    23

    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

  2. #2
    Registered User
    Join Date
    01-27-2006
    Posts
    23
    Hello all,

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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