Conditional formatting w/minutes
 

xl 2000--I am tracking transaction times and would like to use CF to highlite
times more than and less than 2 minutes. I tried to use "cell value" and it
is not recognizing :02, (for instance). What would a formula be that would
work to do this? I think I need to convert the answer to a portion of a day?
I am using a formula to subtract the start time and the end time. The results
are formated as time ie :01, :06, :11, :02 etc.

On Sat, 5 Feb 2005 19:25:02 -0800, Mike R
wrote:

xl 2000--I am tracking transaction times and would like to use CF to highlite
times more than and less than 2 minutes. I tried to use "cell value" and it
is not recognizing :02, (for instance). What would a formula be that would
work to do this? I think I need to convert the answer to a portion of a day?
I am using a formula to subtract the start time and the end time. The results
are formated as time ie :01, :06, :11, :02 etc.

Depending on what you want to do at exactly two minutes:

Cell Value is Less Than 0.0013888888888889


--ron

Thanks Ron that did it for me. Could you tell me what that number represents
or how you got it?

"Ron Rosenfeld" wrote:

On Sat, 5 Feb 2005 19:25:02 -0800, Mike R
wrote:

xl 2000--I am tracking transaction times and would like to use CF to highlite
times more than and less than 2 minutes. I tried to use "cell value" and it
is not recognizing :02, (for instance). What would a formula be that would
work to do this? I think I need to convert the answer to a portion of a day?
I am using a formula to subtract the start time and the end time. The results
are formated as time ie :01, :06, :11, :02 etc.

Depending on what you want to do at exactly two minutes:

Cell Value is Less Than 0.0013888888888889


--ron

Hi!

Ron's suggestion is correct (it always is) but it may be
easier to understand in this application:

You were correct in that you need to convert the value to
a "portion" of a day.

A day is 1440 minutes so you need to convert 2 minutes to
a fractional part of a day:

2/1440 = 0.0013888888888889

So, you could select from the CF dialog: FORMULA IS

=A1<2/1440

The same applies to any measure of time.

hours = h/24
seconds = s/86400

Biff

-----Original Message-----
xl 2000--I am tracking transaction times and would like
to use CF to highlite
times more than and less than 2 minutes. I tried to
use "cell value" and it
is not recognizing :02, (for instance). What would a
formula be that would
work to do this? I think I need to convert the answer to
a portion of a day?
I am using a formula to subtract the start time and the
end time. The results
are formated as time ie :01, :06, :11, :02 etc.
.

Thank you very much. Ron for the formula and Biff for the explaination.
24*60 is 1440, now it seems so simple. Thank you again.
Mike R.

"Biff" wrote:

Hi!

Ron's suggestion is correct (it always is) but it may be
easier to understand in this application:

You were correct in that you need to convert the value to
a "portion" of a day.

A day is 1440 minutes so you need to convert 2 minutes to
a fractional part of a day:

2/1440 = 0.0013888888888889

So, you could select from the CF dialog: FORMULA IS

=A1<2/1440

The same applies to any measure of time.

hours = h/24
seconds = s/86400

Biff

-----Original Message-----
xl 2000--I am tracking transaction times and would like
to use CF to highlite
times more than and less than 2 minutes. I tried to
use "cell value" and it
is not recognizing :02, (for instance). What would a
formula be that would
work to do this? I think I need to convert the answer to
a portion of a day?
I am using a formula to subtract the start time and the
end time. The results
are formated as time ie :01, :06, :11, :02 etc.
.

On Sat, 5 Feb 2005 20:47:01 -0800, Mike R
wrote:

Thanks Ron that did it for me. Could you tell me what that number represents
or how you got it?

As *you* correctly wrote, you need to convert the 2 minutes to a fraction of a
day. There are 24*60=1440 minutes in a day. That number is equal to 2/1440.


--ron