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

Michael G.
Michael G. ✭✭
edited 12/09/19 in Archived 2017 Posts

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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 05/15/17

    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

     

     

  • J. Craig Williams
    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

     

     

This discussion has been closed.