Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
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
Comments
-
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
-
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
-
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
-
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
