Help with difference in time macro

[chin_un_len]

Hello All,

First of all I want to say this is my first time here and I am on my
learning stages of excel.

I have a report I run on a daily basis and need to calculate the time
difference between previous days and the system date.

All my columns are set to autofilter and the Macro needs to look at 3
columns "E" (PRIORITY), "H" (CREATED) AND "I" (SLA)

It should start by inserting a new column next to "H" and name it SLA
Column H is the date to be compared with system date and the difference
is to be inserted in column "I" (SLA)
(this dates are from days past in this format 07/12/2005 4:42:00 PM)

Now the trick here will be to filter column "E" by keywords "MEDIUM"
"HIGH" AND "TOP"

For MEDIUM: Compare H with system date and insert the difference in "I"
with an IF statement, if the difference is greater than 120 hours then
the background of the cell should be colored in RED.
This is based on a 24/5 clock (meaning it does not include Saturday and
Sunday).

For HIGH: Compare H with system date and insert the difference in "I"
with an IF statement, if the difference is greater than 72 hours then
the background of the cell should be colored in RED.
This is based on a 24/5 clock (meaning it does not include Saturday and
Sunday).

For TOP: Compare H with system date and insert the difference in "I"
with an IF statement, if the difference is greater than 4 hours then
the background of the cell should be colored in RED.
This is based on a 24/7 clock (meaning it includes Saturday and
Sunday).

The columns can vary in lenght, no specific number of cells, it varies
from day to day, and each cell needs to be compared.

Please if any body can help, any information will be greatly
appreciated.

 

[Toppers]

Hi,

Try this: it uses NETWORKDAYS function so you need set ATPVBAEN.XLS in
VBE (Tools ==> References)



Sub CompareTimes()

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

Set ws1 = Worksheets("sheet1")
With ws1

Columns("I").Insert Shift:=xlToRight
.Cells(1, "I") = "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


HTH

 

[chin_un_len]

Hi Toppers,

Thank you for your help, I have done what you suggested:

set ATPVBAEN.XLS in VBE (Tools ==> References)

created the macro and ran it, how ever got an error, error indicates:

Compiler error
Syntax error

and the line highlited when the VB editor opens is:

TimeDiff = (networkdays(dt1, dt2) + ((dt2 - Int(dt2)) - (dt1 -

Can you tell from this, where the Syntax error is?
Any help is much appreciated

 

[Toppers]

Hi,
This should be ONE line - it only appears this way because of
"Wrap-round" in the posting.


TimeDiff = (networkdays(dt1, dt2) + ((dt2 - Int(dt2)) - (dt1 - Int(dt1)))) *
24

HTH

 

[chin_un_len]

Hi Topper,

Thank you very much for your help, the macro is working really good

 

[chin_un_len]

Hi Topper,

I was wondering if you could help out once more, the code you gave me
works great but I need to change it a bit, when the time is calculated
and the conditions are checked, it gives the time in decimal, is it
posible for example when it checks for the 120 hours to give the date
the 120 hours are met and not the decimal?

Your help in this matter will be greatly appreciated.

 

[Tom Ogilvy]

change

Columns("I").NumberFormat = "###0.00"
to

Columns("I").NumberFormat = "hh:mm"

and remove the "*24" from each of the formulas.

--
Regards,
Tom Ogilvy


"chin_un_len" <[email protected]>
wrote in message
news:[email protected]...