Using VBA to determine a timespan

[MeggieDoodles]

Hello,

I am trying to work out a bit of code that looks at a cell (formatted as time, preferably "h:mm:ss;@") and will then output a number in another cell if that time is within a timespan.

This is what I want to happen:

Time	Charge
0:15:00	1
0:46:00	2
1:15:01	3

The time is already determined with a formula (=E4-C4)

The timespans are: between 0:00:00 and 0:30:00, between 0:30:01 and 1:00:00, between 1:00:01 and 1:30:00, and so on by half-hour increments until it reaches 2:30:00.

What I have currently is this:

Dim gcell As Range, fcell As Range
Dim cel As Range, cels As Range
Set gcell = Range("g4:g70")
Set fcell = Range("F4:F70")

For Each cel In gcell
    For Each cels In fcell
        If cels.Value > "0:00:00" And cels.value < "0:30:00" Then
            cel = 1
        ElseIf cels.value > "0:30:01" And cels.Value < "1:00:00" Then
            cel = 2
        Else
            cel = 0
        End If
    Next
Next

This is not working at all and I do not know what I'm doing wrong. I know I could have this as a formula within the cell but I would like to avoid that if at all possible.

[MeggieDoodles]

I figured it out! There is a function called TIMEVALUE which did exactly what I needed. Here is the updated code:

Dim gcell As Range
Dim cel As Range
Set gcell = Range("g4:g70")

For Each cel In gcell
        If cel.Offset(0, -1) > TimeValue("1:00:00") And cel.Offset(0, -1) < TimeValue("1:30:00") Then
            cel = 1
        ElseIf cel.Offset(0, -1) > TimeValue("1:30:01") And cel.Offset(0, -1) < TimeValue("2:00:00") Then
            cel = 2
        ElseIf cel.Offset(0, -1) > TimeValue("2:00:01") And cel.Offset(0, -1) < TimeValue("2:30:00") Then
            cel = 3
        End If
Next