Working Comparisions With Midnight

[Ark68]

I have an activity start time variable, lrtime equaling 0.7708333333 (7:00 PM)
I need to compare it to ensure that lrtime falls within the employee's shift. ie lrtime <= sel_crew1_end. The value for sel_crew1_end = 0 (midnight)

sel_crew1_end = WorksheetFunction.VLookup(Me.cb_r1_crew & "1", ws_staff.Range("I5:M28"), 5, False)

sel_crew1_end = 0 (12:00am)
lrtime = 0.7708333333 (7:00pm)

If lrtime > sel_crew1_end Then 'crew 1 can't
        MsgBox "This tournament service is scheduled for after this crew has left."
        cb_r1_crew.Value = ""
        Exit Sub
    ElseIf lrtime < sel_crew1_start Then 'crew 1 can't
        MsgBox "This tournament service is scheduled before this crew starts."
        cb_r1_crew.Value = ""
        Exit Sub
    End If

If my employee works 4:00pm to midnight, 7:00PM is before midnight so should not trigger this condition. But, the code isn't differentiating the difference betwee the midnight at the start of the day or midnight of the start of the next date, which is where the problem stems. Midnight of the same day would trigger the code because 7:00PM is not less than the start of the day, but it is less than the start of the next day (the employee's end time).

How can I overcome this issue?

 

[Toadstool]

Hi Ark68,

I think 0.7708333333 is actually 6:30pm. Time of 7pm would be 0.791666667.

You really need to include the date to be safe for this calculation.

 

[NoSparks]

maybe

If sel_crew1_end = 0 Then sel_crew1_end = 0.999305555555556  '12am -> 11:59pm
If lrtime > sel_crew1_end Then 'crew 1 can't
        MsgBox "This tournament service is scheduled for after this crew has left."
        cb_r1_crew.Value = ""
        Exit Sub
    ElseIf lrtime < sel_crew1_start Then 'crew 1 can't
        MsgBox "This tournament service is scheduled before this crew starts."
        cb_r1_crew.Value = ""
        Exit Sub
    End If

 

[Ark68]

Thanks guys for your replies.

Toadstool, thanks for pointing that out, the incorrect times. Although even with that minor correction, the problem exists, I'm unsure how I would include the date at this point in my code. Semewhere in my code I do have a variable holding the date, how would I add that to the time?

NoSparks, I did actually consider that workaround, but then I got thinking (since sel_crew1_end is dynamic), what happens if it's value is 12:30am or 1:30am. Although it would work, it wouldn't eliminate potential errors down the road.

 

[Toadstool]

Ark68,

You've actually three values: Start of Tournament (lrtime), Crew Hours Start (sel_crew1_Start) and Crew Hours End (sel_crew1_End).

If you've dates for both then it's just a case of adding the dates to the times.

Today is day 43711 so lrtime of today at 7pm becomes 43711.7916666667

sel_crew1 works 4pm to midnight so either:
sel_crew1_Start = 43711.666666666666667 and sel_crew1_End = 43712.0
or if you want to just take it to a second before midnight then
sel_crew1_Start = 43711.666666666666667 and sel_crew1_End = 43711.999988426

Now checking for lrtime being covered by the sel_crew1 time is a check that lrtime >= sel_crew1_Start and lrtime <= sel_crew1_End

 

[NoSparks]

I did actually consider that workaround, but then I got thinking (since sel_crew1_end is dynamic), what happens if it's value is 12:30am or 1:30am. Although it would work, it wouldn't eliminate potential errors down the road.

You can make any time be the next day by adding 1. But you'd need to know at what time you quit adding the 1.
Maybe the earliest of shift starting times ? Something like If thetime < lets say 4am Then thetime = thetime + 1