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" wrote:


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.


--
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=505938