Modified Date Field; The same for all tasks.

Kris V. Le · 04/26/22

Good Morning! I'm having an issue on a sheet I am using as a task manager for a client. I've created a column called "Stale Days" to calculate the amount of time between today and the last modified date. The problem is the entire column is zeros...

When I look at the whole sheet in, all of the modified dates are the same. Regardless of whether or not anything changes in the row. I presume it's one of my automations that run daily, but if the automation does not cause a physical change to the data in the row, I would think it would not update the modified date.

DISREGARD; Leaving this discussion up to help others.

The solution was right in front of my face and apparently my brain stopped working correctly...

I had also created a column "Days Open" so every day it would change causing the modified date to change at the first moment the sheet was accessed for the day...ugh.

Genevieve P. · 04/27/22

Hi @Kris V. Le

I'm glad you were able to get to the bottom of it! Thanks for posting your solution.

Kris V. Le · 05/06/22

Disregard my solution in the first comment. There still seems to be a problem where a user is entering the sheet in the morning and the last modified date is updating for the entire sheet. Can't seem to figure out why....

When I reviewed sheet automations, none of the "Last ran on..." date/times correspond to the modified date/time, so I don't think it's the automation.

When reviewing the activity log, I can see the change to all of the rows in the entire sheet tied to a specific user at a specific time, but the only thing that changed was the modified cell in each row. So I have no idea what's going on.

It could be the function I'm trying to use itself, that's creating a loop of sorts. I'm trying to create a "Stale Days" column that calculates the number of days a task has been sitting with no updates. But I guess if it changes every day for each row, it would also update the modified column. Does anyone have a solution?

Genevieve P. · 05/09/22

Hi @Kris V. Le

Yes, it sounds like your formula is calculating when anyone opens/saves the sheet, which is what is updating all of your rows at the same time.

For example, if you're using a TODAY() function, this will re-calculate and check Today's date when the sheet is opened (see: TODAY Function).

Instead of using the Modified System Column, you could use a Record a Date workflow in a Date column to capture a date when specific cells in the row are updated (such as the Status or Comments), which could then exclude any updates to your formula column.

Cheers,

Genevieve

Kris V. Le · 05/19/22

https://community.smartsheet.com/discussion/comment/328835#Comment_328835

This sounds like a great solution. I will give it a shot and let the community know how it works out.

Modified Date Field; The same for all tasks.

Comments

Categories