Modified Date and Conditional Formatting/Alerts

Joe Soltan · 06/01/18

I have hit a problem that Smartsheet can't seem to deal with.

My goal is to create a sheet that runs conditional formatting based on the differential between 'Modified date' and a TODAY equation.

Firstly I tried to create a TODAY based formula in the conditional formatting tool. However, this acted as a figure once calculated and not as a formula hence it would not automatically update on a daily basis.

Secondly I tried adding a column with a tick box that would change once the differential of TODAY and 'modified date' was past a certain threshold. The plan was to have this drive conditional formatting and alerts. However, once this box automatically ticked it would reset the 'modified date' in a useless circle.

So my question is: Does Smartsheet have the capability to drive conditional formatting and alerts based on a varying calculated TODAY date and a 'modified date'. E.G. After 6 months of inactivity a row will turn red and send an email to the owner of that row.

Thanks, Joe.

J. Craig Williams · 06/01/18

You were on the right track with the column addition.

To answer the question: No, Conditional Formatting can not have formulas, so you need to have a formula that created the value used in the Conditional Formatting

This

=IF(TODAY() < Modified@row, 1, 0)

will be true (1) when the row has not been modified today.

and this

=IF(TODAY(-7) < Modified@row, 1, 0)

will be true when it has not be in the last 7 days.

Note that adding this formula / column will change the Modified date, so test before you save the sheet (when the Modified timestamp is updated)

The row should only update the Modified date for this column when it changes state going forward.

I hope this helps

Craig

Joe Soltan · 06/01/18

Thanks Craig!

I will try this with conditional formatting and alerts. It's similar to what I attempted in the past so I worry that the box ticking itself will trigger the modified date to reset to the current date.

Is there a way of ensuring this formula is copied onto a new column automatically instead of relying on people to enter it themselves?

Thanks,

J. Craig Williams · 06/01/18

Oh crap. It will update when the check mark changes. Hmm.

Let me give it some more thought. I only stopped by to add some enhancement requests and thought the answer was a no-brainer. It was, only the no-brain was mine.

We may be dealing with the Heisenberg Principle here - we can't observe the row (for this case) without affecting it, thus skewing the observation.

For the second part, now moo*, Auto-fill should help for new rows added at the top or bottom. Some bugs described elsewhere.

Craig

*Joey from Friends reference.

Joe Soltan · 06/01/18

Thanks Craig, I believe the only way to get over this would be to enable formulae in the conditional formatting/alerts rules.

Lets hope we can find a way!

Modified Date and Conditional Formatting/Alerts

Comments

Help Article Resources

Categories