Assitance with Recording a Date

Hollyb1985

Hello Community — I am relatively new to Smartsheet and recently inherited a preexisting project plan where many of the rows are already completed. I would like to know when the status for those rows changed to complete as most of the time the end date was not updated to reflect the actual date the status changed to Complete. I created a helper column called "Status Date Change" and created an automation that will record the date the status changes to Complete. I have tested it and it works for rows where status is changed to complete after the automation was activated but not for rows where the status is already marked complete. Is there a way for me to get it to capture the date when a task was updated to Complete in the past? Hopefully that makes sense.

Kerry St. Thomas

…sort of. Maybe. Kind of. Perhaps. Because the trigger in question - "Status Updates To Complete" has already occurred, you unfortunately won't be able to automate this.

If you want a "close enough for hand grenades" approximation, you could manually retype the "Modified Date" value into your Status Date Change column. (If you don't have a "Modified Date" column… add one then save the sheet - that'll populate the column.) This will show THE LAST TIME any row has been modified - the underlying assumption is the last thing that was changed on a given row is the Status. But it's not a perfect answer - if someone changed the status to Complete and then made a bunch of other changes over the following days, the status date will be inaccurate.

If you need it truly accurate, you'll need to go into the Activity Log for the whole sheet or right-click on each status column and "View Cell History" to get the exact date of update. Either way… again, you'll need to manually type it all.

Good luck!

Kerry St. Thomas

…sort of. Maybe. Kind of. Perhaps. Because the trigger in question - "Status Updates To Complete" has already occurred, you unfortunately won't be able to automate this.

If you want a "close enough for hand grenades" approximation, you could manually retype the "Modified Date" value into your Status Date Change column. (If you don't have a "Modified Date" column… add one then save the sheet - that'll populate the column.) This will show THE LAST TIME any row has been modified - the underlying assumption is the last thing that was changed on a given row is the Status. But it's not a perfect answer - if someone changed the status to Complete and then made a bunch of other changes over the following days, the status date will be inaccurate.

If you need it truly accurate, you'll need to go into the Activity Log for the whole sheet or right-click on each status column and "View Cell History" to get the exact date of update. Either way… again, you'll need to manually type it all.

Good luck!

Hollyb1985

Thank you that was helpful. One last question I am using this helper column to create a report to find Tasks Completed in the last week. I have the filter set to filter the report by "Status Date Change" in the last 7 days where status is Complete and its showing me all completed vs the range I requested. Do you know what I might be doing wrong?

Kerry St. Thomas

Make sure the date column is type DATE. Beyond that, I don't know specifically; perhaps you can set & save your report filter so you can then take a screenshot and share, so that others can also chime in?