Amateur formula cell formatting help : colour based on time of the day

[JudgementalJuniorJam]

Hi I work alternating day and night shifts and I want to log my sleep. I want the cells to change their background automatically based on either day or night - and I can specify which hours that is.

So the above is my failed attempt.

I tried to condition cells by formula
" 0<HOUR(V5)<6 "

I think the =HOUR() formula should work but I don’t know how to select the correct cell as I want the Whole COLUMN to have this formatting applied

I tried =HOUR(V$) but that didn’t work

If just leave two conditions as “HOUR(V5)<6” & “HOUR(V5)>20” it colours the night-times but also the the rest of the column which is Thousands of EMPTY cells:

Notice how it colours past the 9:00, below it are empty cells for the rest of the column

So I mean just this:

Any ideas how to get this working?

Am I Not supposed to use conditional formatting for it?

[erAck]

0<HOUR(V5)<6 doesn’t do what you think it would do. It compares 0<HOUR(V5) that yields either 0 (false) or 1 (true), which then is compared against <6, so the expression yields always TRUE, no matter what time value V5 contains.

You want AND(0<HOUR(V5);HOUR(V5)<6) instead.
Note that excludes all values between 00:00 and 00:59:59.999 which quite likely would also be night…
Probably what you really want without colouring also empty cells is

AND(ISNUMBER(V5);0<=HOUR(V5);HOUR(V5)<6)

[JudgementalJuniorJam]

Thank you that worked like a charm!

This now gives this:

[erAck]

Note that HOUR() can not be greater than 23 and the second condition can be simplified to
HOUR(V5)>=20
and the third to
AND(6<HOUR(V5);HOUR(V5)<20)
Both don’t need the ISNUMBER(V5) subcondition as an HOUR() of an empty cell is 0.