Row colorization base on date (current, a day before, and past due)

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I am trying to create the following colorization based on a date field:
1 day prior to [ETA] & [Arrival Date] is empty = yellow
[ETA] = today's date & [Arrival Date] is empty = green
[ETA] date has passed & [Arrival Date] is empty = red

Thank you!
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Caitlyn

Caitlyn

  • 506 Points 500 badge 2x thumb
I think this will work in the colorization formula:

If([Arrival Date]=””,Case([ETA],1=ToDays([ETA]-Today()),”#ffff00”,[ETA]=Today(),”#00ff00”,ToDays([ETA]-Today())<0,”#ff0000”),””)
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
This is what I got:

Formula syntax error

Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.

If([Arrival Date]=ToDate(""),Case([ETA],1=ToDays([ETA]-Today()),  ”#ffff00”,[ETA]=Today(),”#00ff00”,ToDays([ETA]-Today())<0,”#ff0000”),””)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
try this

If(
not Isnull([Arrival Date]), "",
ToDays([ETA]-Today()) = 1,  "#ffff00",
[ETA]=Today(),"#00ff00",
ToDays([ETA]-Today())<0,"#ff0000")

be sure you are using the straight double quotes and not the "66" "99" quotes which you see in Word.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
I did change the quotes, because I thought that was it, but this formula worked. Thank you!!
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Actually, had to change [ETA]=Today() to ToDays([ETA]-Today())>0, but I have what I need. Thanks again!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
OK great, I did not have the time to check your logic, only your syntax.