Is there a conditional formatting rule that shows if a date is more than X years in the past?

LTKT

I have a sheet where I would like one column of dates to be conditionally formatted to show if the date is greater than 5 years in the past. I can only find the function for "in the past" without any way to add to the formula how far in the past. Is it possible to set parameters on past dates?

It seems like this should be a relatively simple conditional formatting rule and I'm guessing other people must have needed this as well, so I am hoping there's a solution. Thank you!

Andrée Starå

Hi @LTKT

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

• You'd have to add a so-called helper column with a formula that you then can reference in the Conditional Formatting rule.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

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!

LTKT

Thank you, Andrée, for letting me know! That's interesting there's no way to perform that simple function.

I am not sure what the formula would need to be to set up the "helper column". I am looking for some examples, but I haven't found anything close enough to my request to give me direction.

Would you mind assisting me with the formula for this column to indicate dates greater than 5 years in the past? I appreciate your guidance!

Andrée Starå

@LTKT

Happy to help!

Try one of these methods. (reference the helper column in the Conditional Formatting rule)

Add Helper column (checkbox) (box gets checked if it was more than five years ago)

=IF(Date@row < TODAY(-1780), 1)

Add helper column (text/number) (you'd see the number of days, and if it's more than 1780 then it's more than 5 years ago)

=TODAY() - Date@row

Make sense?

Did it work?

✅Remember! 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!

LTKT

The first formula to check the box for dates greater than 5 years was successful! I was able to use the conditional formatting from there.

Thank you for that creative workaround and assisting me with correct formula. I appreciate your guidance and timely responses!

Andrée Starå

@LTKT

Excellent!

Happy to help!

✅Remember! 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!