Problem with Conditional Colors on Text

[rponichtera]

I am having a problem with conditional formatting for text colors on screen. I have reused code that is working on other screens but with one big difference. I am trying to do a calculation on the expression and not a straight look at a single field. The code looks like the following:

=if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) <= '01:30:00', blue(),

if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName)))>='01:30:01'

AND (Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting, Branch, ServiceName))) <= '02:00:00', yellow(),

if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName)))>='02:00:01'

AND (Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) <= '02:30:00', red(),

if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) > '02:30:00', black(), rgb(0, 87, 60)))))

I get the correct time for when I use the time calculation in the Field expression but it seems to fail in the conditional text color formatting.

[JaMajka1]

Hi Robert,

try to use frac() function instead of defining date format - here is simpler version of your function:

if(frac(Now()) <= frac(Date#('01:30:00','hh:mm:ss')), blue())

Hope it helps,

Maria

[MayilVahanan]

Hi

Note that, you are subtracting 2 times and comparing with string, its make the expression failed.

=if((Date(Now(),'hh:mm:ss TT') - AVG(AGGR(AVG({$<ServiceName ={'Renew'}>} LongestWaiting), Branch, ServiceName))) <= '01:30:00' <- String

Instead of that, you need to convert the string to time by using time# function. and also, recommend to use time function in the Avg also.

All 3 must have in same format.

[rponichtera]

I have taken both suggestions and tried them . For simplicity I changed the conditional formatting to be on one time frame only. Unfortunately I am still not getting any positive results. The code now looks like:

if((Time#(Now(),'hh:mm:ss') - Time#(AVG(AGGR(AVG({$<ServiceName = 'Renew'>} LongestWaiting), Branch, ServiceName)))) <= Time#('01:30:00','hh:mm:ss'), blue(), red())

[MayilVahanan]

HI

Can you provide some sample data.

[rponichtera]

I have attached a qvf of the app I am working on.