Date Modified

12/20/18 Edited 12/09/19

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?

Comments

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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å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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • mzwillenmzwillen
    edited 08/22/19

    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.

     

  • mzwillenmzwillen
    edited 08/26/19

    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å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?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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 NewcomePaul Newcome ✭✭✭✭✭
    edited 01/23/20

    @Carlos Brito

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


    =IF(DATEONLY([email protected]) <= 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.

    thinkspi.com

    [email protected]

  • 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.

Sign In or Register to comment.