Conditional formatting for past dates

ShirleyBanks

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

Paul Newcome

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)

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.

ShirleyBanks

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

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?

ShirleyBanks

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

Paul Newcome

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)

ShirleyBanks

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

Happy to help. 👍️