Solving a time problem with conditional formatting

[smig123]

I have been trying to solve a problem of how to use conditional formatting to show a certain criteria. I need to know if an event happens between 07:30 and 16:30, I can work this out so that the cells show in green. I cannot solve the problem of formatting in red if the time is after 16:30 but before 07:30.

=IF($E3>=$P$3,IF($E3<$Q$3,TRUE,FALSE))

P3 = 07:30 and Q3 = 16:30


By the way, thanks for letting me join the site, I love using Excel but this problem has beaten me and it's time to ask the clever people :-)

Many thanks for your time.Time Problem.xlsx

[Kaper]

=not(AND($E3>=$P$3,$E3<=$Q$3))
shall be enough

(check in your case if you need >= <= or > < or a mix)

[benishiryo]

welcome to the forum. you could shorten your existing formula to:
=AND($E2>=$P$3,$E2<$Q$3)

and for red, maybe another alternative is:
=OR($E2<$P$2,$E2>=$Q$2)

[smig123]

Kaper

Many thanks, it worked a treat your a genius! I would not have found that in 100 years :-)

[smig123]

Thank you for your help, I will try this when I have more time, Kapers solution worked great, but I will explore your option. Thank you very much.