today() function affects last-modified date

Charlton Rose

We have a last-modified column in one of our sheets. We use it to detect when rows in the sheet have been last modified, driving discussions about the updates.

In this sheet, another column displays a "project completion date," which is just a manually entered date indicating the project owner's best guess as to when the project will be complete. (I guess that's more detail than necessary for this discussion. It's just a date, for this discussion.)

Because date math is hard for humans to do, we recently added another column, next to the completion date, which indicates the number of weeks until that date. This is an automatically generated value, based on the date. Essentially, it's "=(TheDateCell - today()) / 7". It works great, and it makes it super easy for our readers to visualize what the corresponding date really means, relative to other dates.

The only problem is, because our formula makes use of "today()", every time the sheet is opened, the last-modified column for the row updates to today as well! This makes it impossible to continue using that column to track changes.

Is there a way to display the last-modified date of a row while excluding "pseudo-updates" that are formula-based (which, arguably, aren't really updates)?

Werner Gerstacker

Hi @Charlton Rose

I've had the same issue recently and had to implement a convoluted solution for it - it goes like this:

Whenever certain cells are updated in my sheet I send a copy of the row to a helper sheet.

The helper sheet has an additional 'System' column - called Created (Date) - that gets populated automatically when the row is copied into/created in the helper sheet. This way I have a reliable timestamp in the helper sheet when the row was last updated by the owner, not by the TODAY() function in the main sheet.

I then use a =MAX(COLLECT()) function to retrieve the latest update timestamp back into the main sheet.

Actually, I use two of them, the second one has an + "" added to it so that the column, which is marked as a 'Text/Number' column also displays the last update time, not just the last date of the update as a 'Date' column does.

Warning:

I also implemented an even more convoluted mechanism to move old rows out of the helper sheet into an attic sheet where they could be deleted safely if necessary without impacting the whole mechanism.

Charlton Rose

Thanks for sharing your workaround. Sounds horribly complicated and may not be the best experience for a Smartsheet user, but I'm glad you found a way to make it work for you!

Werner Gerstacker

Hi @Charlton Rose

I agree that it was a pain to set up, but not that it's working, I have a reliable way to automatically send reminders to the team members who have not gotten around to update their weekly report by Friday noon. I was worth the effort. And as long as a function like today impacts the 'Modified Date' of every row in my sheet every day I can't see another way.

It would be great, though, having a shortcut by, e.g. being able to push a timestamp into a column of the active sheet based on an automation.