Conditional formatting for past dates

ShirleyBanks · 09/11/24

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 · 09/11/24

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 · 09/11/24

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 · 09/11/24

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 · 09/11/24

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 · 09/11/24

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

Paul Newcome · 09/11/24

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 · 09/11/24

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 · 09/12/24

Happy to help. 👍️

Eric B. · 10/23/24

@Paul Newcome ,

Just picking your brain if I may….

I have two columns with similar date based formulas as the posts above and conditional formatting branched off of them. Yellow format if "older than 30 days" column flags and a red format if "older than 90 days" column flags.

Do you know if there any way to condense the number of columns… say into 1 column? I need to draw attention with aging line items, but would like to reduce the clutter of columns I have on this sheet.

Screenshot of yellow format which is past 30 days:

Thanks in advance for your input!

Paul Newcome · 10/23/24

@Eric B. Really it depends on your needs. The quickest and easiest way to decrease clutter is to hide columns.

But if you need to display the alerts for older than 30/90 days, you can use a nested IF to output a text string along the lines of "30+ Days" or "90+ Days" based on the date and have your conditional formatting trigger on it containing 30 or containing 90.

Eric B. · 10/23/24

@Paul Newcome , Thanks for the suggestion. My nested formulas need a little work, but I was able to install the following:

=IF([Date Created]@row < TODAY(-90), "PAST 90", IF([Date Created]@row < TODAY(-30), "Past 30", "")

So far the formula and the conditional formatting is doing the trick. Thanks!

Formulas and Functions

Formulas and Functions

Conditional formatting for past dates

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary