Trying to conditionally highlight a cell if "Purchase Date" is greater than Today(-1825)

Analyst_IBA

I've tried using the conditional format logic, but any time I try and set a date condition, other than a calendar date or "TODAY" then I get "not a valid date" error message.

I'm trying to set an at-a-glance LCR cell highlight if we exceed 5 years from the purchase date. Sorry to be obtuse, but sure could use a nudge in the right direction. Thanks in advance for any assistance.

Carson Penticuff

I do not know of a way to directly conditionally format using the logic "greater than x days in the past". This, however, can be achieved with a bit of trickery. You will need to modify this based on your exact needs, but the basic idea is to set up two conditional format rules. The first will apply the highlight (background) color of your choice to the cell if it is a date. This will obviously apply to all cells in your column. The second format will then apply a white background (or other color depending on how your column is formatted) if the date is within the last 1825 days, effectively overriding the first format and leaving only dates greater than 1825 days in the past highlighted. Make sure to have the rules in the correct order, as rules above override rules below, which is the trick to making this work.

Andrée Starå

https://community.smartsheet.com/discussion/109175/trying-to-conditionally-highlight-a-cell-if-purchase-date-is-greater-than-today-1825

Hi,

I hope you're well and safe!

To add to Carson's excellent advice/answer.

• You could add a so-called helper column with a formula that checks a box or similar when it's true and reference that in the Conditional Formatting rule.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I personally tend to go with a helper checkbox column in these cases. I would suggest something along the lines of

=IF([Date Column name]@row< TODAY(-1825), 1)

The above would check a box in any row that is more than 1825 days in the past. You would then build your conditional formatting to highlight any row where this box is checked.

If you wanted to get even more accurate... 1825 days isn't quite 5 years. It could be either 1 or two days short depending on where the leap years fall. The below will be truly 5 years to the day every day:

=IF([Date Column name]@row< DATE(YEAR(TODAY()) - 5, MONTH(TODAY()), DAY(TODAY())), 1)

Analyst_IBA

To all that provided feedback, thank you! The work-around certainly worked and we're up and running. Sincerely appreciate the timely responses - hope you all have a great summer.

Paul Newcome

Happy to help. 👍️

Andrée Starå

https://community.smartsheet.com/discussion/comment/391597#Comment_391597

Excellent!

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

dizzy_brain

I want to do something very similar (highlight row if date is x years in the past) but I don't understand what you all mean by a 'helper checkbox' column? Where exactly do I put the formula:

=IF([Date Column name]@row< TODAY(-1825), 1)

And where will this check a box?

( I tried to find explanation of 'helper checkbox column' but couldn't)

Thanks!

Paul Newcome

You would insert a new column and set it as a checkbox type. This new checkbox column that you just created is the "helper" column because it's sole purpose is to "help" with certain functionalities. This column is also where you would put the formula.

dizzy_brain

Thanks, I didn't realise that you can type into the cell over the actual "checkbox"!