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

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

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

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