Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with difference in time macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with difference in time macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with difference in time macro
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 -- chin_un_le ----------------------------------------------------------------------- chin_un_len's Profile: http://www.excelforum.com/member.php...fo&userid=3091 View this thread: http://www.excelforum.com/showthread.php?threadid=50593 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with difference in time macro
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" wrote: 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. -- 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with difference in time macro
Hi Topper, Thank you very much for your help, the macro is working really good -- chin_un_le ----------------------------------------------------------------------- chin_un_len's Profile: http://www.excelforum.com/member.php...fo&userid=3091 View this thread: http://www.excelforum.com/showthread.php?threadid=50593 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with difference in time macro
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. -- 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with difference in time macro
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" wrote in message ... 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. -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difference between times. Start time: 11:30PM End time: 5 AM | Excel Worksheet Functions | |||
Time formula (difference of predicted and actual time) | Excel Discussion (Misc queries) | |||
subtract the time difference from another time difference | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) |