Operators With Times

[Michael Link]

I have a cell (call it A1) containing a date/time stamp of the format mm/dd/
yy hh:mm AM/PM. I'm trying to conditionally format a second cell so that it
turns yellow when the time in the first cell is after (say) 12 noon. In the
conditional formatting box for the second cell I've tried entering the formula

=A1>"12:00"

but the format never kicks in. I thought maybe there was an issue with the
date/time formatting in A1 (you know, that maybe the conditional
formatting formula might work if A1 contained ONLY a time), but
reformatting A1 to show only a time didn't help either. (And in any event, A1
really needs to have the format I've mentioned.)

Does anyone know if the usual <,>,= operators simply don't work with
times? If so, might there be any workarounds? Thanks!

 

[Niek Otten]

You can leave the date out of the formatting, but it is still in the cell
value.

Time is stored as a fraction of a day. So 12:00 is 0.5
In your conditional format, enter the formula:

=MOD(A1,1)>0.5

For other times it is probably easier to enter them in a cell and use that
cell to compare with.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

I have a cell (call it A1) containing a date/time stamp of the format mm/dd/
yy hh:mm AM/PM. I'm trying to conditionally format a second cell so that it
turns yellow when the time in the first cell is after (say) 12 noon. In the
conditional formatting box for the second cell I've tried entering the
formula

=A1>"12:00"

but the format never kicks in. I thought maybe there was an issue with the
date/time formatting in A1 (you know, that maybe the conditional
formatting formula might work if A1 contained ONLY a time), but
reformatting A1 to show only a time didn't help either. (And in any event,
A1
really needs to have the format I've mentioned.)

Does anyone know if the usual <,>,= operators simply don't work with
times? If so, might there be any workarounds? Thanks!

 

[Michael Link]

Thanks, Nick--I'll try the formula you've suggested!