cond formatting times
Posted by cj on March 27, 2001 12:23 AM
I'd like to enter a time in A1. I'd like the cell
to change color an hour before the time entered, and
to remain that color.
I use conditional formatting for dates okay, but I
can't figure it out for times.
Regards
CJ
Posted by Dave Hawley on March 27, 2001 1:54 AM
Hi CJ
Choose "Cell Value is", "Greater than or equal to", =VALUE(TEXT(NOW(),"hh:mm"))-0.041667
Dave
OzGrid Business Applications
Posted by Mark W. on March 27, 2001 5:38 AM
What did you mean by...
> ...and to remain that color.
Posted by Mark W. on March 27, 2001 6:09 AM
By the way...
CJ, there's no need for the VALUE() function in
Dave's suggested formula below. The text value
produced by the TEXT() function is coerced into
a date value by the minus operator. Coercion is
a very powerful concept in Excel. For example,
a boolean value can be coerced to a numeric value
by adding 0 (=TRUE+0 produces 1). A number
can be coerced into a text value using the
concatenation operator, & (=1+2&"" produces "3").
The text representation of a date can be coerced
into it's corresponding date value (="1/1/00"+0
produces 36526).
Posted by Dave Hawley on March 27, 2001 4:49 PM
Let's split hairs!
Cj, There is no need to use "+0" in Marks example you can use the VALUE() formula instead. In fact there is no need to even use the Excel interface at all you could use the TIME function within VBA housed within the Worksheet module using the Calculate Event. or We could link it to the OnTime event.
Sorry CJ, just trying to make the point(there always more than one way to skin a cat) that the VALUE function is as good as any way!
Dave
Posted by bj on March 27, 2001 6:10 PM
Shouldn't this be "Less than or equal to", =VALUE(TEXT(NOW(),"hh:mm"))+0.041667 ?
Also, format will not automatically trigger when the time arrives (unless VBA is used) - need to press F9 to recalculate.
Posted by Dave Hawley on March 27, 2001 7:43 PM
Hey cj, that sounds like something my good friend Mark W would say :o)
Both formulas will do the same thing though :o)
But, yes to make it truly automated you would have to use VBA. perhaps Mark can help us here ?
As cj is using the same for dates (With TODAY()I assume)I'm guessing that another Volatile function well do!
Dave
OzGrid Business Applications
Posted by bj on March 27, 2001 8:43 PM
Wrong. The formulas do not do the same thing.
:
Rather than attempting an explanation, I suggest you test them.
Posted by Dave Hawley on March 27, 2001 9:50 PM
Yes, you are right!
Rather than attempting an explanation, I suggest you test them.
Buy why the attitude ?
Rather than attempting to suggest VBA why not post the solution incorporating VBA ?
OzGrid Business Applications
Posted by bj on March 28, 2001 12:31 AM
Re: Yes, you are right!
Attitude ???
Will leave the VBA solution in your capable hands.
Posted by Mark W. on March 28, 2001 7:30 AM
CJ, as I was saying...
The VALUE() function in Dave's formula below is not necessary (redundant) because of the way Excel's minus operator coerces the text representation of the current time:
=VALUE(TEXT(NOW(),"hh:mm"))-0.041667
If you were to choose such a function as your solution all you'll need is:
=TEXT(NOW(),"hh:mm")-0.041667
