Failure In Identifying That A Time Falls Within A Range

[Ark68]

Please consider this code:

    t_base = ws_data.Range("W" & rn)   '  "> 6:00 PM
    l_len = Len(ws_data.Range("W" & rn))  ' 9
    For x = 1 To l_len
        If IsNumeric(Mid(t_base, x, 1)) Then Exit For
    Next
    t_time = TimeValue(Mid(t_base, x))  ' 0.75
    t_crew_row = Application.WorksheetFunction.Match((cb_sig2_crew.Value & "1"), ws_staff.Range("I:I"), 0)    ' 9

    ty9s = CDate(WorksheetFunction.VLookup((Me.cb_sig2_crew.Value & "1"), ws_staff.Range("I5:M21"), 4, False))
    ty9e = CDate(WorksheetFunction.VLookup((Me.cb_sig2_crew.Value & "1"), ws_staff.Range("I5:M21"), 5, False))

    If t_time <= ty9s Or t_time >= ty9e Then 'CDate(Me.tb_sig_sttime.Value)
        MsgBox cb_sig2_crew.Value & " is ineligible to provide this service.", vbInformation, "SERVICE NOTICE"
        cb_sig2_crew.Value = ws_data.Range("X" & rn)
    End If

The idea of this is to check to see whether t_time falls within a time range defined by variables ty9s and ty9e.

In the tests that are failing ....
t_time = 0.75 (6:00 PM)
ty9s = 0.6666666666666666667 (4:00 PM)
ty9e = 0 (12:00 AM)

In this example, 6:00 PM falls between 4:00 PM and midnight. However, it does not pass through as such. It gets trapped and the user message is displayed.

This code works in all other cases where ty9e isn't midnight, so I suspect this is the cause of the problem.

Is anyone able to suggest a solution to this problem?

 

[yky]

If you substitute t_time with 0.75 and ty9e with 0 in "t_time >= ty9e", you get "0.75 >= 0" which is true. So, the MsgBox code is executed.

 

[Ark68]

Hi yky ... yes. That is indeed true, but it's "not" supposed to be in this context. (?)
What I am trying to do is determine if a certain time, t_time, falls with an employee's 8 hr shift, in this case between 4:00PM and midnight. So does t_time fall before ty9s or after ty9s? If it does, it doesn't fall in the range. But in the context of the shift, 6:00PM does fall within the range. Its after ty9e but less than midnight. I think the date is playing a factor.

I can't change the value of ty9e at the source, but maybe if I changed ty9e to 11:59PM it would work? Not sure how I would do that as I would only have to do this if ty9e was midnight.

 

[yky]

24:00, which is equivalent to 0:00, should be 1, not 0.

Instead of using t_time >= ty9e, try this:

t_time >= IIF(ty9e=0,1,ty9e)

 

[Ark68]

yky ... that is awesome! I was expecting some huge complicated manipulation, but this seems to have done the trick! Thank you!

I'm not sure what you mean though by "24:00, which is equivalent to 0:00, should be 1, not 0."
I see that was an important concept as that knowledge was used in your solution. IIF is a first for me!

 

[yky]

I'm not sure what you mean though by "24:00, which is equivalent to 0:00, should be 1, not 0."

Glad it works for you.

In A1, you enter "0:00". Then, copy and paste down to A25 as series. You'll see a series of 0:00, 1:00, 2:00, 3:00... all the way to 00:00 (this is 24:00, not the first 0:00). Copy A1 to A25 and paste it "by value" to B1. You'll see values from 0, 0.041667 all the way to 1, which is 24:00 or 12 AM or 0:00.

When time passes 23:59, it becomes 24:00 but no one uses 24:00. Everyone uses 0:00. Interestingly, 0:00 is expressed as 0 in Excel and 24:00 is 1. So, 24:00 is actually not 0:00, in Excel's sense.