Welcome!

Modified Date Field; The same for all tasks.

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.

Find more posts tagged with

Comments

Genevieve P.

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

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.

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Automation Not Working
I have a sheet set to notify myself and another user when ANY CHANGE is made to the sheet. Neither of us received the notification. Any suggestions on why this is not working? It has been a while since this form was used, but it worked previously.
Why am I not getting reminders?
My reminder in smartsheet automation is set to trigger every day starting 12/05/2025 at 8am (gmt+1) and the condition is to send the reminder to the contact in the contact list if the reminder flag is set to 1 and the status is not one of DONE. Currently on the sheet, a task has the reminder flag set to 1 and the status is…
Membership/ Access question
Hi Everyone, My IT has a lot of confusion on what we access we really need to have for certain people. What is the difference between a "Member" and a "Licensed User?" We have a lot of sheets that have owners who do all of the changes of the sheet. I know that they need to be licensed users. But then, we have a lot of…