Modified Date Field; The same for all tasks.

克丽丝诉勒 · 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.

克丽丝诉勒 · 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 coulduse a Record a Date workflowin 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

克丽丝诉勒 · 05/19/22

Hi @Kris V. Le<\/a> <\/p>

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. <\/p>

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<\/a>). <\/p>

Instead of using the Modified System Column, you could use a Record a Date workflow <\/a>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. <\/p>

Cheers,<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Kris V. Le\",\"userID\":140076}}},{\"insert\":\" \\nYes, 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. \\nFor example, if you're using a TODAY() function, this will re-calculate and check Today's date when the sheet is opened (see: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/function\\\/today\"},\"insert\":\"TODAY Function\"},{\"insert\":\"). \\nInstead of using the Modified System Column, you could \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481986-set-the-current-date-with-record-a-date-action\"},\"insert\":\"use a Record a Date workflow \"},{\"insert\":\"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. \\nCheers,\\nGenevieve \\n\"}]","format":"rich","dateInserted":"2022-05-09T09:07:50+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-07-28T21:24:09+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/328835#Comment_328835","embedType":"quote"}"> 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