Colored Dots Formula

I'm trying to use a formula to populate a column with colored dots based off of a date.

This is what I'm trying to do

Red - Date prior to TODAY
Yellow - Date within the next 90 days from TODAY
Green - Date greater than 90 days from TODAY
Gray - Date is blank

I keep getting the unparseable error.

This is my formula:

IF([Contract Expiration Date]@row < TODAY(0), "Red", IF([Contract Expiration Date]@row < TODAY(90), "Yellow", IF([Contract Expiration Date]@row ISDATE, "Gray", "Green")))

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Your ISDATE() syntax is a little off. I also reordered your conditions a little to avoid an error if something other than a date is entered.

    =IF(ISDATE([Contract Expiration Date]@row), IF([Contract Expiration Date]@row < TODAY(0), "Red", IF([Contract Expiration Date]@row < TODAY(90), "Yellow", "Green")), "Gray")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!