Working Comparisions With Midnight

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)

Rich (BB code):
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)

Rich (BB code):
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?
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
maybe
Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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