Caclulate if Now() is between 2 times across midnight Excel

Question

I feel a bit noob asking this but been scratching my head trying to figure out a solution for this problem. (ADHD hitting hard as well)

I have a list of people that are schedule to work in various time segments, some of them with shifts that crosses midnight, and after comparing it with a different data set know if the person if the person is working or not.

1. Column A contains the list of people,

2. Column B and C contains the days off, combined with + =Today()

3. Column D contains the time when the shift begins, (As it time only, added =Today)

4. Column E contains the time when the shift ends,

5. Column F will indicate if that specific person is in their day off or they need to work, used the following code for it

   =IF(OR(B3=(TEXT(TODAY(),"ddd")),C3=(TEXT(TODAY(),"ddd"))),"Week-Off","Workday")

In column G I want to know if the person is working or they are absent after matching it with another table using this formula

=IF(F2="Workday",IF(AND(Now()>=$E2,(Now()<=$F2),"Scheduled","Off-Shift")," ")

So far the code has worked for everyone who has schedules that ends at 11:59 pm, but when the shift ends at midnight or it crosses midnight it provides incorrect results (Shows Offshift instead of Scheduled)

In Short I want to know if =Now() is between 2 times, when is in the same day or the shift crosses midnight.

Code (Asume Now() is 1:30 am)

=IF(F4="Workday",IF(AND(Now()>=$D4,(Now()<=$E4),"Scheduled","Off-Shift")," ")

Expected Result

 Schedule       

Actual Result

 Off-Shift

As on now i feel is a pretty easy solution but unable to wrap around a solution, and somehow my brain is focus on using IF. As an extra bonus if it can be done by Excel-VBA it will be a plus

Answer 1

After looking a bit further found the solution in the following post: Determine if a given time is between start and end time