Date Modified

CamCrichton

I have a sheet with a list of assets. There is a pull down menu that defines the status - I need to know when the menu is set to "complete". I can do that easily with a filter - but I want to cull the entries before a specific date.

I have a Report that uses the 'is greater than-date modified' but I get results from all over the place - totally inconsistent - so I'm not sure what Smart Sheets constitutes as 'modified'.



Alternately I try placing a system column directly in the sheet called 'Modified' - this updates every row with the current date every time the sheet is saved, which explains the previous statement.

Surely this is not the correct behaviour for 'modified' as it becomes completely useless if it considers every row to modified by simply opening and closing the file.



Can anyone shed some light on how to achieve what I am after?

Andrée Starå

Hi Cam,

Unfortunately, it's not possible at the moment, but it's a great idea!

The system column called "Modified" changes everytime anything on the row changes.

Please submit an Enhancement Request when you have a moment.

Two possible workarounds could be to either add a date column that you can use to register the date manually when it's changed to Complete or use a third-party solution like Zapier or similar to automate the date input.

Would that work?

Have a fantastic day & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

mzwillen

Hi Andree,

Can you please elaborate? I also created a sheet with a list of asset columns and I need confirmation as to how the Modified column actually works.

Without going into too much detail, my sheet contains a last modified date column, a last modified plus 45 days column (an equation tied to the last modified column), and a column with manager names.

I created an automated workflow that will request an update from the manager whenever today equals the last modified plus 45 column. 

Now, this will definitely work if the manager has updates to make. However, will the Modified column still update if the manager submits their update without changing anything (i.e. they submit an update request showing no changes to the current assets)? Please advise.

 

Thank you

Andrée Starå

Happy to help!

I came to think about another solution that maybe could work and that would be to lock the rows thru automation automatically.

If they don't make any changes, they will get a prompt about that there aren't any changes, but they could still submit it, but it wouldn't change anything in the sheet so the Modified column wouldn't change.

Would that work?

Hope that helps!

mzwillen

Hi Andree,

How would locking the rows automatically help? Would it change the last modified date (I tested and looks like the answer is no)?

I need to send an update request to the manager column if it's been 45 days since they last updated their asset. Therefore, I need a way to get the Last Modified Date column to update, even if the manager submits an update request with no changes (meaning there were no updates to their asset).

I need an automated system push as we have over 1,000 assets and I can't manually request an update.

 

You are definitely correct-I just sent an update request to myself and submitted without any changes. Smartsheet notified me (on the form) that no changes were made, and allowed me to submit anyway.

I then received a notification on my sheet that a form was submitted with changes so I refreshed the sheet, but the last modified date did not change.

 

How can I get this to do what I need it to do? I've considered making another column with a counter that would update whenever an update request was submitted (just so something in the row would change) but this isn't possible either.

 

mzwillen

So I've tried a few other approaches and none really work.

-creating an automation to send the update request to the manager and an approval to me (so I can toggle between Submitted and Approved, a new column) but this doesn't work because both emails go out at the same time (i.e. the approval email goes out whether or not the person in the manager column has made an update).

-doing the opposite (automation to me and then email to managers) is no good because it will reset the clock every 45 days, whether or not the manager has made an update.

-having the person in the manager column submit a comment of 'no updates' but comments don't drive changes to Date Last Modified

-it's not possible to run reports or to use functions on the row action indicators (the leftmost mini columns of a report where you can see attachments, pending update requests, etc)

-I added a new column with a formula that toggles from 0 to 1 on a date (or checks and unchecked a checkbox, same idea), but formula-driven changes to values don't change the last Modified Date. If this worked, I could set up my sheet to send out the update request and then toggle this formula to reset the counter, with the drawback that the clock would reset whether or not the manager made an update so I couldn't report on people who don't provide updates. However, this didn't work (i.e. formula driven changes to fields don't change the last Modified Date) so can't move forward with this method anyway.

The only solution I have is to have a new 'counter' row, so when the update request goes out, the person in the manager column will have to change this counter (representing the number of times they've received an update request). This will change the Modified Date as a change to the row will actually have occurred.

 

Hoping someone has a more elegant solution. Thank you

Andrée Starå

I got another idea for a possible workaround.

My idea is to send an Approval Request at the same time as the update request. It would change the approval status to something like Update request 1 sent, and that would also update the last modified date. We could add multiple approval requests that would change and change the modified date.

Make sense?

Would that work/help?

Carlos Brito

Hi!

I have the same issue. The modified column has no functionality for workflows if it changes every time I save the smartsheet or a formula is updated.

I want to be able to send a alert when a row is not modified since X days, but this is not currently possible with the behavior of the modified column.

I hope you can solve this.

Regards,

Carlos

Paul Newcome

@Carlos Brito

You could add in a checkbox column that uses a formula such as

=IF(DATEONLY(Modified@row) <= TODAY(-30), 1)

The bold portion (-30) would be changed to however many days you want to flag the row after. Then you could build your automation to run if the box becomes checked.

Rob Cooper

Hi,

It has been a while since this was last responded on. Have there been on updates in SmartSheets since these posts, which have rectified the issue with the modified column?

I was thinking about creating a new 'Modified Column' and creating a rule to only update this column when selected columns are amended (not created). I have over 60 columns I would want to track for amendments though, and don't think the rule will allow for that many entries.

PeterNYU

I see some unusual behavior when working with the Modified column. I have a bunch of rows with cells (actually full columns) which are linked to another sheet. I also have a set of rows which I did not link to another sheet.

When I update one of the rows with linked cells, all of the rows with the linked cells update the date in the Modified column - to the dame date/time (even though I didn't change anything in most of those rows).

The rows where I don't have linked cells update the Modified date independently when I make a change and save it (as one would expect).

Bizarre... I can't imagine there is any reason for this behavior so I would call it a bug.

PeterNYU

One other potentially related issue. When I create a dashboard with more than one underlying sheet, I loose all cell conditional formatting.

Ashley Pierce

https://community.smartsheet.com/discussion/comment/237822#Comment_237822

Hi Paul - I like this idea; is there a way to utilize it to build an automation to refresh the sheet when the checkbox is checked?

Andrée Starå

Hi @Ashley Pierce

I hope you're well and safe!

Here's a possible workaround or workarounds

• Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ChrisMcG

Hey there: Is this feature still in the works? without workarounds, I mean: Just a straightforward "X column last modified date" field

Paul Newcome

@ChrisMcG You can set up a Record A Date automation.