Excel VBA Check if time is between two time values, including midnight spans-CodePudding

I've come into a problem that I am really struggling to find the solution to. I have 3 time values (without the date component) stored as strings, e.g. 01:00. Two of these values represent an after and before time, and the other is the current time. I need to check if the current time is between the after and before times (inclusive).

This can simple be checked with the below:

Dim res As Boolean
res = TimeValue(tVal) >= TimeValue(tAfter) And TimeValue(tVal) <= TimeValue(tBefore)

This works for time values that do not span midnight. If for example you have an after time of 23:00, a before time of 04:00 and a current time of 02:00, this will not work.

How do you check if a time is between two time values that span midnight? Or even how do you check if the two time values actually span midnight (as checking if the before or after times are </> 00:00 doesn't seem to work either)?

CodePudding user response：

If the interval doesn't exceed 24 hours, this should do:

If TimeValue(tBefore) <= TimeValue(tAfter) Then
    ' Within one day.
    res = TimeValue(tVal) >= TimeValue(tBefore) And TimeValue(tVal) <= TimeValue(tAfter)
Else
    ' Crossing Midnight.
    res = TimeValue(tVal) >= TimeValue(tBefore) Or TimeValue(tVal) <= TimeValue(tAfter)
End If

CodePudding user response：

This is hard to answer without seeing your data. If you only have time data, ie with no date, then you're going to have to make some assumptions. A simple assumption might be: if tAfter is less than tBefore, add a day (or vice versa depending on which came first). You could do the same with tVal.

If the difference between tBefore and tAfter can be more than 12 hours, then you'll need something more sophisticated.

Skeleton code for the simple example would be something like this:

Dim tBefore As String, tAfter As String, tVal As String
Dim b As Double, a As Double, v As Double

tBefore = "20:00"
tAfter = "02:00"
tVal = "01:00"

b = TimeValue(tBefore)
a = TimeValue(tAfter)
If a < b Then a = a   1
v = TimeValue(tVal)
If v < b Then v = v   1

If v >= b And v <= a Then
    Debug.Print "Within"
Else
    Debug.Print "Outside"
End If