Conditional formatting for past dates

Hello,

I need some assistance in creating a conditional format where "Active" will turn red in the Status column if the Last Reviewed date is at least five days in the past. This should only happen for the cells that are currently gold.

None of the date inspection choices in conditional formatting seem to fit this criteria. Yesterday I tried "today-5" as a condition but it just calculated it and then hard-wired that into the condition (i.e., today-5=9/5/24 and it plopped in that date). I checked today and the formula did not dynamically recalculate using today's date.

This doesn't seem like a complex thing, so I'm thinking I'm missing something obvious.

I would prefer not to use a "helper" column if possible (seen in other discussions).

Thanks very much for help :)

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The only way to get this is with a helper column that can be hidden from view after setting up. You would use a formula to flag the rows that meet the criteria and then use conditional formatting based on the hidden column being flagged.

    =IF(AND(Status@row = "Active", [Did patient attend scheduled appointment]@row = "Future", [Last Reviewed]@row < TODAY(-5)), 1)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!