Prevent changing last modified date by formulas with TODAY

Hello 'smartsheet',

we are using some formulas including the TODAY-function.

Example:

Mark a task as "overdue": =IF(AND([End Date]1 < TODAY(), Status1 <> "done"), "overdue", "-")
After that we use conditional formatting (red color ...).

But now each day, a task becomes "overdue" the first user of the day is registered under "modified by" - and this is also visible in the activity log (great feature!).

It should be possible to prevent this.

Maybe this could be solved by an column property option 'don´t effect modivication user/date'.

Kind regards

Michael

Find more posts tagged with

Comments

J. Craig Williams

Michael,

Change your formula to check for "done" and THEN check the date.

=IF(Status1 = "done", "-", IF([End Date]1 < TODAY(), "overdue", "-" ))

I did not test that formula, so consider this pseudo code.

Craig

Dan Davis

This is good advice .. reshuffling the sequence and logic of a formula can often solve formulas that don't seem to do what you want them to do. I had been trying to make a formula work that would rely on a date stamp, somewhat like this case, that I learned isn't available in Smartsheet. I need to revisit that formula and reshuffle the logic like you have done here, it might just work for me.

Thanks! Dan

Michael G.

Craig,

thank you - changing the formula reduces this effect, but doesn´t solve the problem. There are still a lot of unnecessary entries in the activity log!

Michael

J. Craig Williams

Michael,

Are you saying that you get activity log entries for a past due item every day?

That makes sense. I misunderstood and thought you were getting hits on completed items that were due in the past. Your problem is with the incomplete ones.

I'll need to think about that. I was almost expecting to cry "bug!" but I verified that the Modified time stamp does not change when I have a formula that results in one thing and then later replace it with the same value without the formula.

For example:

="bob"

and then later, I just type

bob

... no change to the Modified time stamp, which is what I would expect.

Can you check one of the offending result's cell history and see if it updating too?

Craig