Modified Date Field; The same for all tasks.

Kris V. Le
Kris V. Le ✭✭✭
edited 04/26/22 in Smartsheet Basics

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.

Comments

  • 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
    Kris V. Le ✭✭✭
    edited 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?

  • 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
    Kris V. Le ✭✭✭

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