[RESOLVED] Check If Time Value is between Two Times

[fba1]

Never had to check this before and for some reason its not working the way I want it to.

Basically I am trying to see if a time from an array is between two times from a SQL query.

So

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim timeAry(0 To 47) As Variant
Dim k, e As Integer
Dim strSql As String
 
k = 3
 
e = 0
 
' Setup the Array
timeAry(0) = "12:00 AM"
timeAry(1) = "12:30 AM"
timeAry(2) = "1:00 AM"
timeAry(3) = "1:30 AM"
timeAry(4) = "2:00 AM"
timeAry(5) = "2:30 AM"
timeAry(6) = "3:00 AM"
timeAry(7) = "3:30 AM"
timeAry(8) = "4:00 AM"
timeAry(9) = "4:30 AM"
timeAry(10) = "5:00 AM"
timeAry(11) = "5:30 AM"
timeAry(12) = "6:00 AM"
timeAry(13) = "6:30 AM"
timeAry(14) = "7:00 AM"
timeAry(15) = "7:30 AM"
timeAry(16) = "8:00 AM"
timeAry(17) = "8:30 AM"
timeAry(18) = "9:00 AM"
timeAry(19) = "9:30 AM"
timeAry(20) = "10:00 AM"
timeAry(21) = "10:30 AM"
timeAry(22) = "11:00 AM"
timeAry(23) = "11:30 AM"
timeAry(24) = "12:00 PM"
timeAry(25) = "12:30 PM"
timeAry(26) = "1:00 PM"
timeAry(27) = "1:30 PM"
timeAry(28) = "2:00 PM"
timeAry(29) = "2:30 PM"
timeAry(30) = "3:00 PM"
timeAry(31) = "3:30 PM"
timeAry(32) = "4:00 PM"
timeAry(33) = "4:30 PM"
timeAry(34) = "5:00 PM"
timeAry(35) = "5:30 PM"
timeAry(36) = "6:00 PM"
timeAry(37) = "6:30 PM"
timeAry(38) = "7:00 PM"
timeAry(39) = "7:30 PM"
timeAry(40) = "8:00 PM"
timeAry(41) = "8:30 PM"
timeAry(42) = "9:00 PM"
timeAry(43) = "9:30 PM"
timeAry(44) = "10:00 PM"
timeAry(45) = "10:30 PM"
timeAry(46) = "11:00 PM"
timeAry(47) = "11:30 PM"
' End Array
 
Do Until e = 48
 
rst.Open strSql, cnn, adOpenStatic
 
Range("A" & k).Value = ""
 
Range("A" & k).Value = timeAry(e)
 
Range("B" & k).Value = 0
 
Do Until rst.EOF
 
If (timeAry(e) >= rst.Fields!Sun_Start And timeAry(e) <= rst.Fields!Sun_End) Then Range("B" & k).Value = Range("B" & k).Value + 1
 
rst.MoveNext
 
Loop
 
rst.Close
 
k = k + 1
 
e = e + 1
 
Loop
 
cnn.Close
 
Set rst = Nothing
Set cnn = Nothing

Returns

12:00 AM 18
12:30 AM 26
1:00 AM	26
1:30 AM	26
2:00 AM	26
2:30 AM	27
3:00 AM	27
3:30 AM	27
4:00 AM	27
4:30 AM	27
5:00 AM	27
5:30 AM	27
6:00 AM	27
6:30 AM	27
7:00 AM	27
7:30 AM	20
8:00 AM	20
8:30 AM	19
9:00 AM	18
9:30 AM	0
10:00 AM 0
10:30 AM 15
11:00 AM 17
11:30 AM 18
12:00 PM 18
12:30 PM 26
1:00 PM	26
1:30 PM	26
2:00 PM	26
2:30 PM	27
3:00 PM	27
3:30 PM	27
4:00 PM	27
4:30 PM	27
5:00 PM	27
5:30 PM	27
6:00 PM	27
6:30 PM	27
7:00 PM	27
7:30 PM	20
8:00 PM	20
8:30 PM	19
9:00 PM	18
9:30 PM	0
10:00 PM 0
10:30 PM 15
11:00 PM 17
11:30 PM 18

For some reason 9:30 and 10:30 get 0's. Anyone have any ideas?

[fba1]

LOL nevermind figured it out

Sub Test()

Dim timeAry(0 To 47) As Variant


' Setup the Array
timeAry(0) = #12:00:00 AM#
timeAry(1) = #12:30:00 AM#
timeAry(2) = #1:00:00 AM#
timeAry(3) = #1:30:00 AM#
timeAry(4) = #2:00:00 AM#
timeAry(5) = #2:30:00 AM#
timeAry(6) = #3:00:00 AM#
timeAry(7) = #3:30:00 AM#
timeAry(8) = #4:00:00 AM#
timeAry(9) = #4:30:00 AM#
timeAry(10) = #5:00:00 AM#
timeAry(11) = #5:30:00 AM#
timeAry(12) = #6:00:00 AM#
timeAry(13) = #6:30:00 AM#
timeAry(14) = #7:00:00 AM#
timeAry(15) = #7:30:00 AM#
timeAry(16) = #8:00:00 AM#
timeAry(17) = #8:30:00 AM#
timeAry(18) = #9:00:00 AM#
timeAry(19) = #9:30:00 AM#
timeAry(20) = #10:00:00 AM#
timeAry(21) = #10:30:00 AM#
timeAry(22) = #11:00:00 AM#
timeAry(23) = #11:30:00 AM#
timeAry(24) = #12:00:00 PM#
timeAry(25) = #12:30:00 PM#
timeAry(26) = #1:00:00 PM#
timeAry(27) = #1:30:00 PM#
timeAry(28) = #2:00:00 PM#
timeAry(29) = #2:30:00 PM#
timeAry(30) = #3:00:00 PM#
timeAry(31) = #3:30:00 PM#
timeAry(32) = #4:00:00 PM#
timeAry(33) = #4:30:00 PM#
timeAry(34) = #5:00:00 PM#
timeAry(35) = #5:30:00 PM#
timeAry(36) = #6:00:00 PM#
timeAry(37) = #6:30:00 PM#
timeAry(38) = #7:00:00 PM#
timeAry(39) = #7:30:00 PM#
timeAry(40) = #8:00:00 PM#
timeAry(41) = #8:30:00 PM#
timeAry(42) = #9:00:00 PM#
timeAry(43) = #9:30:00 PM#
timeAry(44) = #10:00:00 PM#
timeAry(45) = #10:30:00 PM#
timeAry(46) = #11:00:00 PM#
timeAry(47) = #11:30:00 PM#
' End Array

i = 0

Do Until i = 47

If Sgn(#7:00:00 AM# - timeAry(i)) + Sgn(#11:30:00 AM# - timeAry(i)) = 0 Then Debug.Print timeAry(i)

i = i + 1

Loop

End Sub

[anhn]

You should reconsider:

Sub Test()
    Const TimeStart = #7:00:00 AM#
    Const TimeEnd = #11:30:00 AM#
    Dim timeAry(0 To 47) As Date
    Dim i As Long
    
    For i = 0 To 47
        timeAry(i) = TimeSerial(0, i * 30, 0)
    Next
    For i = 0 To 47
        If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
            Debug.Print timeAry(i)
        End If
    Next
End Sub

or even shorter:

Sub Test()
    Const TimeStart = #7:00:00 AM#
    Const TimeEnd = #11:30:00 AM#
    Dim timeAry(0 To 47) As Date
    Dim i As Long
    
    For i = 0 To 47
        timeAry(i) = TimeSerial(0, i * 30, 0)
        If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
            Debug.Print timeAry(i)
        End If
    Next
End Sub

[fba1]

You should reconsider:

Sub Test()
    Const TimeStart = #7:00:00 AM#
    Const TimeEnd = #11:30:00 AM#
    Dim timeAry(0 To 47) As Date
    Dim i As Long
    
    For i = 0 To 47
        timeAry(i) = TimeSerial(0, i * 30, 0)
    Next
    For i = 0 To 47
        If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
            Debug.Print timeAry(i)
        End If
    Next
End Sub

or even shorter:

Sub Test()
    Const TimeStart = #7:00:00 AM#
    Const TimeEnd = #11:30:00 AM#
    Dim timeAry(0 To 47) As Date
    Dim i As Long
    
    For i = 0 To 47
        timeAry(i) = TimeSerial(0, i * 30, 0)
        If (timeAry(i) > TimeStart) And (timeAry(i) < TimeEnd) Then
            Debug.Print timeAry(i)
        End If
    Next
End Sub

Ahh nice didn't even think about using the TimeSerial function thanks anhn, and shorter more efficient code is always nice

[fba1]

Actually it dose not work the way I need it to.

For Example if the Array is 12:00:00 AM and the StartDate is 8:00:00 PM and the EndDate is 1:00:00 AM it still returns false instead of true.