Forum Discussion

KarenHenke's avatar
KarenHenke
Qrew Cadet
5 years ago

Help with formula: color rows based on duration field

I'm stumped and could use some help writing a formula please.

Goal: To color rows in a report based on the number of days a record has been in "On Hold" status.

I have a date field [Date placed on hold] that I've used to calculate the duration [# days on hold] (a Formula-Duration field).

I'd like the formula to say 
if [# Days on hold] is 14-29 color the row yellow
if [# Days on hold] is 30-59 color the row orange
if [# Days on hold] is greater than 59 color the row pink  

Can someone help me write this formula? I'd be ever so grateful.

------------------------------
Karen Henke
------------------------------
Formulas and functions
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    try this

    IF(
    [# Days on hold] >=14 and [# Days on hold] <= 29, "yellow",
    [# Days on hold] >=30 and [# Days on hold] <= 59, "orange",
    [# Days on hold] >=60, "pink")

    I think that all those color names should work, but you can also use those hex codes.  Another QSP, Laura, from IDS,  did this nice app here https://laurahillier.quickbase.com/db/bhy8pumuk?a=q&qid=-1000728





      

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • KarenHenke's avatar
      KarenHenke
      Qrew Cadet
      5 years ago
      Thank you Mark!
      I'm getting this error:


      ------------------------------
      Karen Henke
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        OK, I did not realize that this was a Duration field type.
        try this

        var number DaysOnHold = ToDays([# Days on hold]);

        IF(
        $DaysOnHold >=14 and $DaysOnHold <= 29, "yellow",
        $DaysOnHold>=30 and $DaysOnHold <= 59, "orange",
        $DaysOnHold >=60, "pink")

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------