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

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…