Failure In Identifying That A Time Falls Within A Range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Please consider this code:
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top