Conditional Formatting - Dates in the past

Sazzle4244

Hi,

I want to add a conditional format on a column holding dates that will highlight those dates which are over three years ago.

I thought I would be able to pick "is not in the last (days)" and enter 1095, however it only shows "is in the last (days)"

For now I have had to add two conditions:

"is in the past"

AND

"is less than 01/11/20"

But this would mean I would have to regularly update the conditions in the formatting to amend the date it is looking at, which is not ideal as I actually have several columns that require the same conditions.

I use to use excel and the conditional formatting on there would allow me to format based on a date and a formula...for instance "is less than [todays date]-1095" but I can't seem to get my head around the conditional formatting on Smartsheet to do the same....if anyone has any ideas I would be very grateful

brianschmidt

It is odd now that you mention it that there's not a "not" option for days in conditional formatting. I would suggest two things:

A. Submitting a improvement request to Smartsheet for this feature: https://app.smartsheet.com/b/form?EQBCT=739aa75f30ca43a8a22eb53e4da7d409&_gl=1*261rcn*_ga*MTkzNTU1OTA3LjE2Nzc1OTU0MjQ.*_ga_ZYH7XNXMZK*MTcwNjcwOTc5Ni4yNTcuMS4xNzA2NzEyNzM5LjU4LjAuMA..&_ga=2.188484065.856354191.1706627191-193555907.1677595424

B. You can create a helper column (can be hidden once set up) that can be used to trigger the conditional formatting. I would make it a checkbox with a formula something like this:

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

This will check the box if the date column is over 3 years ago. Then, you can set up the conditional formatting based on rows with check marks in your helper column.

Hope this helps!:)