Modified Date and Conditional Formatting/Alerts

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.

Find more posts tagged with

Manufacturing

Sheets

Formulas

Solution Center

IT & Operations

Product Development

Conditional Formatting

Project Management

Update Requests

Email Notifications

Comments

J. Craig Williams

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

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

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

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…