Can I get some help with this conditional formatting?

[Peter h]

I have 3 columns (F, N, and R) with timestamps for data entered into other columns. The time format is "m/dd/yyyy HH:mm". I want to be able to easily differentiate which ones are during Day Shift hours and which ones are Night Shift hours, so I want to format the cells differently depending on what hour is in the time, omitting any blank cells. So if the time is between 06:00-18:00 the cell will be highlighted one color, and if it is between 18:00-06:00 it would be highlighted another color. It is probably obvious, and may not matter, but keep in mind that Night shift (18:00-06:00) goes from one day to the next (ie: 1/28/2018 18:00 - 1/29/2018 06:00). Thanks so much for any help you got for me.

 

[mole999]

i might use three formats, 0 to 0.25, then 0.25 to 0.75 the the same format from 0.75 to 1

 

[barry houdini]

If the time is exactly 6 AM or 6 PM in which category would they fall? Assuming 6 AM exactly is a day and 6 PM exactly is a night you can use this formula for conditionally formatting days

=AND(HOUR(A2)>=6,HOUR(A2)<18)

Now for nights if you check the above first (you can change the order of the CF conditions as required) you only have to determine that A2 is non-blank (or a number) for it to be a night shift so for nights

=ISNUMBER(A2)

 

[Peter h]

If the time is exactly 6 AM or 6 PM in which category would they fall? Assuming 6 AM exactly is a day and 6 PM exactly is a night you can use this formula for conditionally formatting days

=AND(HOUR(A2)>=6,HOUR(A2)<18)

Now for nights if you check the above first (you can change the order of the CF conditions as required) you only have to determine that A2 is non-blank (or a number) for it to be a night shift so for nights

=ISNUMBER(A2)

This is correct. 5:59pm is a day and 6:00pm is a night. I tried putting the formula in the CF Formula bar and it doesn't change the formatting of the Cell. I'm not exactly sure what I'm doing wrong. My data starts in F7, and goes down the column. The same is true for Columns N and R.

 

[markmzz]

This is correct. 5:59pm is a day and 6:00pm is a night. I tried putting the formula in the CF Formula bar and it doesn't change the formatting of the Cell. I'm not exactly sure what I'm doing wrong. My data starts in F7, and goes down the column. The same is true for Columns N and R.

Maybe this small modification in Barry's formula:


=IF(ISNUMBER(F7),AND(HOUR(F7)>=6,(HOUR(F7)<18)))

=IF(ISNUMBER(F7),NOT(AND(HOUR(F7)>=6,(HOUR(F7)<18))))​

Markmzz

 

[Peter h]

Maybe this small modification in Barry's formula:


=IF(ISNUMBER(F7),AND(HOUR(F7)>=6,(HOUR(F7)<18)))

=IF(ISNUMBER(F7),NOT(AND(HOUR(F7)>=6,(HOUR(F7)<18))))​

Markmzz

That worked perfectly. Thanks