Conditional Formatting using midnight

[ktbja]

Hi All, Hoping I can get some help here. I have successfully used this forum many times for answers but am having trouble with this one so thought I best post my own question.

I am trying to use conditional formatting for employee's shift hours. If they work between 22:00 to midnight they get 1.25 penalty rates. And if they work between Midnight to 7am they get 1.50 penalty.

I am basically trying to highlight cells a particular colour if an end time finishes between specific times as per below. This is so Managers can clearly see who is working at what penalties however I am having trouble as it runs through midnight.

This is the conditions I am after.
Between 22:15 to 00:00 = Highlighted Red
Between 00:15 to 6:45 = Highlighted Yellow
Between 7:00 to 22:00 = no formatting required

As per attached example, I can only get between 22:15 to 23:45 to work. Not sure if I am on the right track or completely off the mark!

It would be greatly appreciated if anyone has some answers for me.

Thanks

[kvsrinivasamurthy]

Pl see attached file.

[sktneer]

@ kvsrinivasamurthy
It would be very much helpful if you also share the formula being used in the solution provided so that other members can view the formula suggested without opening the attachment.

[kvsrinivasamurthy]

@sktneer
As per your sugession,
For A2
For Yellow

Please Login or Register  to view this content.

For Red

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Duplicate,deleted

[sktneer]

@ kvsrinivasamurthy
Thanks a lot.
Actually right now I am accessing this thread on my mobile and you know that opening an attachment on mobile is certainly not very convenient.
Hope you understand.

[ktbja]

@kvsrinivasamurthy
Perfect!! Thanks so much, really appreciate it