Changing text color for a formula duration field

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I have a formula duration field that calculates elapsed time (hh:mm) between a start time and a finish time. If the elapsed time is a negative or greater than 8 hours, I want to highlight the text as red.

My field [Duration: Engaged to Recovered] is calculated from the following fields

ToTimestamp([Date Recovered],[Time Recovered])-ToTimestamp([Date ESG Engaged],[Time ESG Engaged])

If for some reason my users enter a recovery time prior to the engagement time resulting in a negative (i.e., -46:15) or recovery takes significantly longer than the norm (i.e. 20:18), I want to highlight this with a red font.

All the formula's I've tried give errors that I can't use a "duration" < "number" syntax. I've tried creating a new formula text field, formula number field ... I'm stuck.

Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,438 Points 50k badge 2x thumb
try this

IF(

[Duration: Engaged to Recovered]>hours(8),"red",

[Duration: Engaged to Recovered]<hours(0),"red")

Photo of Xavier Fan

Xavier Fan, Champion

  • 450 Points 250 badge 2x thumb
Mark's answer is for a row colorization formula.

If you wanted to color the field itself - then:

1) create a Formula - Text field - say it's called [Duration: Engaged to Recovered (Text)]

2) check the checkbox for "Allow some HTML tags to be inserted in the field"

3) use a formula similar to this: (If the # of hours in [Duration: Engaged to Recovered] is less than 0, or greater than 8, then display the value in bold red)

If (

ToHours([Duration: Engaged to Recovered]) < 0

or

ToHours([Duration: Engaged to Recovered]) > 8,

"<div style='color: #bb0000; font-weight: bold;'>" & ToText([Duration: Engaged to Recovered]) & "</div>",

ToText([Duration: Engaged to Recovered])

)
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
Wahoo - worked perfectly
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
Wahoo - again Mark, thanks!