Conditional Formatting with Times

[trosasco]

I'm trying to do a conditional formatting that will highlight any times past 5:00 PM.. I tried this but it didn’t work: =IF(a:a<"5:01:00 PM")

See attached. can someone help please?

Thank you,
Trish

Book1.xlsx

[etaf]

=IF(a:a<"5:01:00 PM")

this is how to turn text into a time value
=TIMEVALUE("5:01 PM")
this extracts the time from the date/time cell
=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

so you can use , in a conditional format
=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<TIMEVALUE("5:01 PM")

see attached

[trosasco]

Actually the value on A4 should've been the highlighted one not the other 2 because they are before 5pm.

[etaf]

=IF(a:a<"5:01:00 PM")
is showing A less than for TRUE

but all you need to do is change the < to >

[Tony Valko]

I'm trying to do a conditional formatting that will highlight any times past 5:00 PM.. I tried this but it didn’t work: =IF(a:a<"5:01:00 PM")

You say you want to highlight if the time is PAST 5:00 PM but your formula is trying to test times BEFORE 5:01 PM.

In your file the cells also contain a date so you need to account for that.

Try this formula:

=MOD(A2,1)>TIME(17,0,0)

[trosasco]

You are both right. I always get this < and > confused. I made the change but I'm still having trouble figuring this out. See attached. This is the original report. Notice that D17, D25 and D39 are not supposed to be highlighted but the ones I highlighted in yellow are. How is this happening? Please help.

Trish

Expenses Spreadsheet.xlsx

[Tony Valko]

Try it like this...

Select the *entire* range D2:D84 starting from cell D2.
Cell D2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=MOD(D2,1)>TIME(17,0,0)

Click the Format button
Select the Fill tab
Select the desired fill color
OK out

[etaf]

your selecting the whole of the column
D:D
and You have the formula as D2
so its one row out

also you dont put the range in the formula

SO if you change the range in the formula
from D2:??
TO
Just
D1
it will work

[trosasco]

Yes thank you for noticing that. I did that part all kinds of ways.. Finally got it to work. Thank you. This thread is closed.. Phew!!