excel - Conditional formatting: Accounting for day rollover

Question

I'm trying to make a spreadsheet that shows me which machines are available to me in real-time. The only issue is I do not know how to account for the day rollover. I want the "status" color to be green when the current time is within the time range "start time" and "est. end time" for that given row.

The issue occurs when the start time is in the previous day (i.e. 22:45) and the current time is in the next day (i.e. 01:00). Fig. 1 shows the template working while Fig. 2 shows how the time messes with the "AND" formula. The "AND" function seems to fail me because the function does not account for the rollover in the day. Any help on this will be appreciated!

P.S. I want it set up to where I just need to put the military time in the "start time" column and not have to put the date.

Below are my current conditions for the cells in the "status" column:

Red =NOT(ISBLANK($I2))

Orange =AND($A$1>$F2,$A$1<$G2)

Green =OR($A$1<$F2,$A$1>$G2,$F2=" ",$F2=0)

Working Template

Overlapping Day

Answer 1

等待大神答复