| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time comparison problem
Greetings,
I am stumped with this one. It must be in the time calculation, however, I can't seem to make it work. What I have is a workbook with about twenty years of computer data for several variables. They are displayed like this: Time Data6/28/01 0:58 -0.499 6/28/01 1:58 -0.499 6/28/01 2:58 -0.499 6/28/01 3:58 -0.499 6/28/01 4:58 -0.499 6/28/01 5:58 -0.499 6/28/01 6:58 -0.499 6/28/01 7:58 -0.499 What I am trying to do is write a macro that inserts a blank row at 6:00 am, 12:00 pm, 6 pm and midnight. Here is the code I have tried: Dim SCC As Integer, SCR As Integer, WCR As Integer Dim TST As Single, TST1 As Single SCR = ActiveCell.Row ' SCR is Start Cell Row SCC = ActiveCell.Column ' SCC is Start Cell Column WCR = SCR ' initialize Work Cell Row from Start Cell Row Do Until Cells(WCR, SCC) = "" 'TST = Hour(Cells(WCR, SCC)) + (Minute(Cells(WCR, SCC)) / 60) 'TST1 = (Hour(Cells(WCR, SCC)) * 60 + Minute(Cells(WCR, SCC))) / 60 'TST = Hour(Cells(WCR, SCC)) 'TST1 = Hour(Cells(WCR + 1, SCC)) TST = ((Cells(WCR, SCC) - Fix(Cells(WCR, SCC)))) * 24 TST1 = ((Cells(WCR + 1, SCC) - Fix(Cells(WCR + 1, SCC)))) * 24 If TST <= 6 Then <<<<<<<<<<<<<<< This comparison doesn't work ElseIf TST1 = 6 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If If TST <= 12 Then ElseIf TST1 = 12 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If I have marked the comparison dat doesn't work. Actually they all don't work, however with the data set I have attached, that one would be the only one that could work. I have used watches on TST and TST1. They show values which should process appropriately, however, they don't. As you can see I had several attempts with converting the date to an hour, however, all were unsuccessful. So I have to believe that it is something wrong in the way I am comparing the hour and the 6, etc. Any ideas on how to fix this would be appreciated. Thanks in advance! Ray |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Time comparison problem
Ray
it could be that you split the date and time. then you could also have the windows daylight savings time UTC problem.. send me you marco and some real data, I'll look at it Ray "Ray Batig" wrote in message ink.net... Greetings, I am stumped with this one. It must be in the time calculation, however, I can't seem to make it work. What I have is a workbook with about twenty years of computer data for several variables. They are displayed like this: Time Data6/28/01 0:58 -0.499 6/28/01 1:58 -0.499 6/28/01 2:58 -0.499 6/28/01 3:58 -0.499 6/28/01 4:58 -0.499 6/28/01 5:58 -0.499 6/28/01 6:58 -0.499 6/28/01 7:58 -0.499 What I am trying to do is write a macro that inserts a blank row at 6:00 am, 12:00 pm, 6 pm and midnight. Here is the code I have tried: Dim SCC As Integer, SCR As Integer, WCR As Integer Dim TST As Single, TST1 As Single SCR = ActiveCell.Row ' SCR is Start Cell Row SCC = ActiveCell.Column ' SCC is Start Cell Column WCR = SCR ' initialize Work Cell Row from Start Cell Row Do Until Cells(WCR, SCC) = "" 'TST = Hour(Cells(WCR, SCC)) + (Minute(Cells(WCR, SCC)) / 60) 'TST1 = (Hour(Cells(WCR, SCC)) * 60 + Minute(Cells(WCR, SCC))) / 60 'TST = Hour(Cells(WCR, SCC)) 'TST1 = Hour(Cells(WCR + 1, SCC)) TST = ((Cells(WCR, SCC) - Fix(Cells(WCR, SCC)))) * 24 TST1 = ((Cells(WCR + 1, SCC) - Fix(Cells(WCR + 1, SCC)))) * 24 If TST <= 6 Then <<<<<<<<<<<<<<< This comparison doesn't work ElseIf TST1 = 6 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If If TST <= 12 Then ElseIf TST1 = 12 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If I have marked the comparison dat doesn't work. Actually they all don't work, however with the data set I have attached, that one would be the only one that could work. I have used watches on TST and TST1. They show values which should process appropriately, however, they don't. As you can see I had several attempts with converting the date to an hour, however, all were unsuccessful. So I have to believe that it is something wrong in the way I am comparing the hour and the 6, etc. Any ideas on how to fix this would be appreciated. Thanks in advance! Ray |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Time comparison problem
There were a number of things wrong. I had to remove the 0.499 from the data
to get it to work. Your "if else" didn't make sense. Sub splittime() Dim SCC As Integer, SCR As Integer, WCR As Integer Dim TST As Single, TST1 As Single SCR = ActiveCell.Row ' SCR is Start Cell Row SCC = ActiveCell.Column ' SCC is Start Cell Column WCR = SCR ' initialize Work Cell Row from Start Cell Row Do Until Cells(WCR + 1, SCC) = "" 'TST = Hour(Cells(WCR, SCC)) + (Minute(Cells(WCR, SCC)) / 60) 'TST1 = (Hour(Cells(WCR, SCC)) * 60 + Minute(Cells(WCR, SCC))) / 60 'TST = Hour(Cells(WCR, SCC)) 'TST1 = Hour(Cells(WCR + 1, SCC)) MyTime = Cells(WCR, SCC) Midnight = DateSerial(Year(MyTime), Month(MyTime), Day(MyTime)) TST = (MyTime - Midnight) * 24 NextTime = Cells(WCR + 1, SCC) TST1 = (NextTime - Midnight) * 24 If Int(TST1 / 6) - Int(TST / 6) = 1 Then '<<<<<<<<<<<<<<< This comparison doesn't work Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert WCR = WCR + 1 End If WCR = WCR + 1 Loop End Sub "Ray Batig" wrote: Greetings, I am stumped with this one. It must be in the time calculation, however, I can't seem to make it work. What I have is a workbook with about twenty years of computer data for several variables. They are displayed like this: Time Data6/28/01 0:58 -0.499 6/28/01 1:58 -0.499 6/28/01 2:58 -0.499 6/28/01 3:58 -0.499 6/28/01 4:58 -0.499 6/28/01 5:58 -0.499 6/28/01 6:58 -0.499 6/28/01 7:58 -0.499 What I am trying to do is write a macro that inserts a blank row at 6:00 am, 12:00 pm, 6 pm and midnight. Here is the code I have tried: Dim SCC As Integer, SCR As Integer, WCR As Integer Dim TST As Single, TST1 As Single SCR = ActiveCell.Row ' SCR is Start Cell Row SCC = ActiveCell.Column ' SCC is Start Cell Column WCR = SCR ' initialize Work Cell Row from Start Cell Row Do Until Cells(WCR, SCC) = "" 'TST = Hour(Cells(WCR, SCC)) + (Minute(Cells(WCR, SCC)) / 60) 'TST1 = (Hour(Cells(WCR, SCC)) * 60 + Minute(Cells(WCR, SCC))) / 60 'TST = Hour(Cells(WCR, SCC)) 'TST1 = Hour(Cells(WCR + 1, SCC)) TST = ((Cells(WCR, SCC) - Fix(Cells(WCR, SCC)))) * 24 TST1 = ((Cells(WCR + 1, SCC) - Fix(Cells(WCR + 1, SCC)))) * 24 If TST <= 6 Then <<<<<<<<<<<<<<< This comparison doesn't work ElseIf TST1 = 6 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If If TST <= 12 Then ElseIf TST1 = 12 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If I have marked the comparison dat doesn't work. Actually they all don't work, however with the data set I have attached, that one would be the only one that could work. I have used watches on TST and TST1. They show values which should process appropriately, however, they don't. As you can see I had several attempts with converting the date to an hour, however, all were unsuccessful. So I have to believe that it is something wrong in the way I am comparing the hour and the 6, etc. Any ideas on how to fix this would be appreciated. Thanks in advance! Ray |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Time comparison problem
Joel,
Worked perfectly. Looks like I had some issues with serial dates as well. Thanks!! Ray "Joel" wrote in message ... There were a number of things wrong. I had to remove the 0.499 from the data to get it to work. Your "if else" didn't make sense. Sub splittime() Dim SCC As Integer, SCR As Integer, WCR As Integer Dim TST As Single, TST1 As Single SCR = ActiveCell.Row ' SCR is Start Cell Row SCC = ActiveCell.Column ' SCC is Start Cell Column WCR = SCR ' initialize Work Cell Row from Start Cell Row Do Until Cells(WCR + 1, SCC) = "" 'TST = Hour(Cells(WCR, SCC)) + (Minute(Cells(WCR, SCC)) / 60) 'TST1 = (Hour(Cells(WCR, SCC)) * 60 + Minute(Cells(WCR, SCC))) / 60 'TST = Hour(Cells(WCR, SCC)) 'TST1 = Hour(Cells(WCR + 1, SCC)) MyTime = Cells(WCR, SCC) Midnight = DateSerial(Year(MyTime), Month(MyTime), Day(MyTime)) TST = (MyTime - Midnight) * 24 NextTime = Cells(WCR + 1, SCC) TST1 = (NextTime - Midnight) * 24 If Int(TST1 / 6) - Int(TST / 6) = 1 Then '<<<<<<<<<<<<<<< This comparison doesn't work Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert WCR = WCR + 1 End If WCR = WCR + 1 Loop End Sub "Ray Batig" wrote: Greetings, I am stumped with this one. It must be in the time calculation, however, I can't seem to make it work. What I have is a workbook with about twenty years of computer data for several variables. They are displayed like this: Time Data6/28/01 0:58 -0.499 6/28/01 1:58 -0.499 6/28/01 2:58 -0.499 6/28/01 3:58 -0.499 6/28/01 4:58 -0.499 6/28/01 5:58 -0.499 6/28/01 6:58 -0.499 6/28/01 7:58 -0.499 What I am trying to do is write a macro that inserts a blank row at 6:00 am, 12:00 pm, 6 pm and midnight. Here is the code I have tried: Dim SCC As Integer, SCR As Integer, WCR As Integer Dim TST As Single, TST1 As Single SCR = ActiveCell.Row ' SCR is Start Cell Row SCC = ActiveCell.Column ' SCC is Start Cell Column WCR = SCR ' initialize Work Cell Row from Start Cell Row Do Until Cells(WCR, SCC) = "" 'TST = Hour(Cells(WCR, SCC)) + (Minute(Cells(WCR, SCC)) / 60) 'TST1 = (Hour(Cells(WCR, SCC)) * 60 + Minute(Cells(WCR, SCC))) / 60 'TST = Hour(Cells(WCR, SCC)) 'TST1 = Hour(Cells(WCR + 1, SCC)) TST = ((Cells(WCR, SCC) - Fix(Cells(WCR, SCC)))) * 24 TST1 = ((Cells(WCR + 1, SCC) - Fix(Cells(WCR + 1, SCC)))) * 24 If TST <= 6 Then <<<<<<<<<<<<<<< This comparison doesn't work ElseIf TST1 = 6 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If If TST <= 12 Then ElseIf TST1 = 12 Then Cells(WCR + 1, SCC).Select Selection.EntireRow.Insert End If I have marked the comparison dat doesn't work. Actually they all don't work, however with the data set I have attached, that one would be the only one that could work. I have used watches on TST and TST1. They show values which should process appropriately, however, they don't. As you can see I had several attempts with converting the date to an hour, however, all were unsuccessful. So I have to believe that it is something wrong in the way I am comparing the hour and the 6, etc. Any ideas on how to fix this would be appreciated. Thanks in advance! Ray |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time Comparison | Excel Programming | |||
| String Comparison using Like Problem | Excel Programming | |||
| Time comparison formula | Excel Discussion (Misc queries) | |||
| Time comparison | Excel Programming | |||
| Time/Data Comparison | Charts and Charting in Excel | |||
Linear Mode
