VBA Solution To Determine If One Time Range Overlaps Another

[Ark68]

I am stuck on a concept, and I'm hoping someone might be able to provide me a nudge on the best VBA approach to do what I need to do.

I have a service time range (svc_start and svc_end). For this example svc_start = 9:00AM and svc_end = 8:00PM

I have a 10 staff scheduled throughout the day, their shifts are defined by shift_start and shift_end, values gathered from a range of values in a data range. What I need to do is determine whether any one particular employee, based on their shift, is able to provide a service within the service time range. Does an individual's shift overlap the service time?

eg

employee 1, shift_start =7:00A - 3:00P. This employee could provide a service within their scheduled hours 8:00A - 3:00P Response: YES
employee 2, shift_start = 12:00P - 8:00P. This employee could provide a service within their scheduled hours 12:00P- 8:00P Response: YES
employee 3, shift_start = 6:00P - 2:00A next day. This employee could provide a service within their scheduled hours 6:00P- 8:00P Response: YES
employee 2, shift_start = 9:00P - 5:00A next day. This employee could not provide a service within their scheduled hours as the service range is outside this person's shift Response: NO

Not certain how to code this logic. I will hazard a guess it's a pretty direct solution, but I can't wrap my head around it at this time.

 

[Eric W]

The basic logic to determine if 2 time ranges overlaps goes like this. Assume the first range has start time S1, and end time E1, and the second range has start time S2, and end time E2. Then figure out the smallest of the end times, MIN(E1, E2) and the largest of the start times MAX(S1, S2) and subtract: MIN(E1, E2) - MAX(S1, S2). If that gives you a positive number, then the ranges overlap.

In your case, where the shifts can overlap 2 days, it's FAR easier to include the dates as part of the time. March 11, 9:00 PM to March 12, 5:00 AM. Otherwise you have to do some complicated additional calculations about adding or subtracting 24 hours.

Let me know if you need help with specific code.

 

[Ark68]

Thanks Eric! That was the nudge I was was looking for. I just couldn't wrap my head around the logic. I have learned early on in my project that it is best to include the date with my times by default. It doesn't seem to do any harm, but without it, crazy results happen when times are represented into the next day.
Thanks for the offer to assist further. I'll post my success.