Conditional Formatting - look at time?

[thiazi]

Is there a way to have conditional formatting look at time values, such as in hh:nn:ss format? I have a field that produces a value of 10 minutes, and I want to set a conditional format to say anything over 10 minutes needs to have the text changed, background color changed, etc. However, 0:10:00 doesn't seem to work and #0:10:00# is 12:10:00 AM.

 

[shudini]

when you say the field 'produces a value of 10 minutes', what do you mean? how are the produced 10 minutes being displayed?

 

[thiazi]

Hi there, sorry.. it's displayed as 00:10:00 and the field is set to hh:nn:ss format.

 

[shudini]

just use minute([MyTimeField]) to return the integer value of the minutes and use it in expression of the conditional formatting

If
Expression Is minute([MyTimeField])>10
...

 

[thiazi]

worked great thanks!

 

[tjsnydernj]

just use minute([MyTimeField]) to return the integer value of the minutes and use it in expression of the conditional formatting

If
Expression Is minute([MyTimeField])>10
...

Have a similar situation except I need to include the other values as well hours, minutes, and seconds. How would I go about doing that?

 

[shudini]

any boolean function passed to Expression Is should work. so using
Expression Is (minute([MyTimeField])>10 and hour([MyTimeField])>2) will work.

If you can give a little more detail on your problem I can try to suggest a more specific solution.

 

[tjsnydernj]

On one table I am using the following to populate a field that needs to display the elapsed time of an action (a shuffle):

TimeOfShuffle: Format([ShuffleStart]-1-[ShuffleEnd],"hh:nn:ss")

The resulting field is then part of a query that is used to create the report. In the report I am using conditional formatting to change the color of the text (00:03:03) if the elapsed time is outside of established standards. For example, if the duration of the shuffle is longer than 3 minutes or shorter than 2 minutes, the elapsed time will be displayed in red. If it is between 2 and three minutes, it will be displayed in green.

 

[shudini]

if when you say 2-3 minutes you mean from 2:00-2:59 not including 3:00, then simply color the text red by default and then use the following conditional expression to set the text green:

if
Expression Is minute([shuffleLength]=2)

if you want to include also where it's 3:00, or any other permutation, then just mix and match the boolean statements:

if
Expression Is minute([shuffleLength]=2 or (minute([shuffleLength])=3 and second([shuffleLength])=0)

if you want to deal with a range, then you could also use >, <, etc.