Formatting cells based on today's date

Ty Werven

I’m working with a date column in Smartsheet and want to apply conditional formatting based on the number of days since the event occurred. The goal is to automatically color-code the cells relative to today’s date.

For example:

• Blue if the event occurred more than 730 days ago.
• Green if the event occurred between 365 and 730 days ago.

I would also like to apply yellow formatting if a date is within one month of reaching either one year or two years since the event occurred. In other words, the cell should turn yellow if the date is within about 30 days of the 1-year mark or the 2-year mark.

I’m trying to determine the best way to implement this logic using Smartsheet formulas and conditional formatting rules.

Anyone have any ideas?

Dan Beres

Hi @Ty Werven,

There are two ways to go about this. You can do some Conditional formatting work as shown below:

The issue is when you try to get anything like the yellow formatting just 30 days before it will always look at the highest rule in the hierarchy, so if we want it to go back to green that can be a little bit of a headache. You would really only be able to get white/regular formatting, green formatting, and blue formatting easily this way.

The way I do recommend is using a "helper column":
All this helper column does is give you a days since X event happened. I set this one as

=TODAY()-[Date of Last CL]@row

And it gives me the number of days since whatever row I am looking at. Here is an example of what the rules would look like then:

Note: you will need to make a change to the default/top rule. If everything is automatic you will get an error like this:

I would just change something like the task bar color and then keep everything else automatic as to not accidentally overwrite any manual formatting. You can adjust the conditional formatting to work on a single cell or the entire row. If you are looking for it to track that color for each of the 4 types you showed above, the most straightforward way is going to be having 4 helper columns. You can then right click the cell → convert to column formula and right click the header → and hide the columns so that they work in the background for formatting, but don't clutter your sheet.

They will unfortunately contribute to the cell limit sheets have, but that is 500,000 cells at the time of my posting in grid view (and more in table view).
Let me know if I can clarify anything further or if you have any questions!