Enter Times In Conditional Format

  • Thread starter Thread starter Mack
  • Start date Start date
M

Mack

I can't seem to get the right syntax to enter a time parameter in a
conditional format.
If I want A1 to display red when B1 is later then 3:00 PM, I tried this in
A1,
=b1>3:00
or
=b1>15:00
or
=b1>3:00 PM
and nothing is accepted. I get an error message saying that the formula is
wrong.
If I use the formula, =b1>C1and put 3:00 PM or 15:00 in C1 it works.

Why can't I hard code the time?

Thanks,
Mack
 
Mack,

Excel treats dates/times as numbers ; 1 day = 1, therefore 1 hour = 1/24.
Therefore,

=B1>0.625

(15:00 = five eights, fortunately).

HTH,
Andy
 
Thanks a lot !

I would love if someone would show me where in Help this is mentioned. I
could not find it anywhere.

Mack
 
I can't seem to get the right syntax to enter a time parameter in a
conditional format.
If I want A1 to display red when B1 is later then 3:00 PM, I tried this in
A1,
=b1>3:00
or
=b1>15:00
or
=b1>3:00 PM
and nothing is accepted. I get an error message saying that the formula is
wrong.
If I use the formula, =b1>C1and put 3:00 PM or 15:00 in C1 it works.

Why can't I hard code the time?

Thanks,
Mack
Click to expand...

You are very close:

=b1>"15:00"

You could also try

=b1>time(15,0,0)


--ron
 
This is a much better idea. However, I couldn't get the former to work.

Rgds,
Andy
Click to expand...

Any idea why? I'm using XL2002. Or perhaps there is something about your
environment? Transition options?


--ron
 
Andy,

I'm also using 2K and it (=b1>"15:00")
also *doesn't* work for me either.

Your =b1>time(15,0,0) works fine, but I think your original =b1>15/24 is
what I'll use since it reminds me how time works in XL.

Thanks again

Mack

Using XL2K. Which transition options might make a difference?

Rgds,
Andy
 
Using XL2K. Which transition options might make a difference?

Rgds,
Andy
Click to expand...

Well, something very interesting, Andy.

Yesterday it worked. Today it doesn't work.

But =B1>--"15:00"
and
=B1>VALUE("15:00")
both seem to work.

And I also recall some HELP information indicating that it is not as "safe" to
use time (or date) strings directly in a variety of functions, compared with
ensuring that the value is a number (e.g. as above or TIMEVALUE or TIME(h,m,s),
etc.) or using a cell reference.

So maybe that's what I/we've run in to.




--ron
 
Back
Top