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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How would you specify for only the rows in gold since there are other rows with the same date and even other rows with later dates.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sorry I should have included I have another column "Did patient attended scheduled appointment" and if the cell contains "Future" then I turn the date in "Last Reviewed" gold to signify I need to circle back and check. I'm trying to create a visual trigger (i.e., turn "Active" red) so I'm alerted that it's been X amount of days since I did circle back and look. This way I can stay on top of closing out cases with minimum delay.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So to make sure I understand…

    If [Did patient attended scheduled appointment] is "Future" and if [Last Reviewed] is more than 5 days in the past, you want to highlight the [Status] column red?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Essentially, yes. Ideally, I would like the word "Active" to turn red for that row. But I'll take a red highlight ;)

  • 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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you so much for taking time to answer my question, Paul. I hadn't thought of hiding the helper column, duh!! Much appreciated :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!