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
-
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!
Answers
-
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!
-
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.
-
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!
-
Essentially, yes. Ideally, I would like the word "Active" to turn red for that row. But I'll take a red highlight ;)
-
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!
-
Thank you so much for taking time to answer my question, Paul. I hadn't thought of hiding the helper column, duh!! Much appreciated :)
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!