automatically register completion date

matthew chan

how do i get a smartsheet to automatically register completion date in a cell based on condition to be achieved in another cell?

for example, a task to automatically register the completion date on the day a task is approved by a manager

Andrée Starå

Hi @matthew chan

Unfortunately, it's not possible at the moment to lock the date, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could use,

• The third-party service, Zapier, is an excellent option for this scenario.
• The copy row automation to copy/lock the date in another sheet and then use VLOOKUP or INDEX/MATCH to get it back to the main sheet.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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

Mike Wilday

Andree's solution about copying a row to a sheet would indeed give you a hard date that you could use vlookup with a cross-sheet reference to return the date it was moved. You'd need a new sheet with a system column for date created. Then you could vlookup for that task name in your time sheet and return the time created field.

For more on copying rows see:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/move-and-copy-rows

For more on Cross sheet refrerences see:

https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

For more on Vlookup see:

https://help.smartsheet.com/function/vlookup

matthew chan

Thanks Andree and Mark for your advise. I'm new to smartsheet. Just trying to figure out what is possible and what's not.

Andrée Starå

@matthew chan

You're more than welcome!

Have a look here at my post on how to do it without Zapier.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

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

Mike Wilday

Please let us know if you get stuck along the way or come up with new questions. Have a good day.

Kevin Segrist

Hi,

Thank you for this thread, I was also looking for a way to populate a Completed Date column when a Done checkbox is checked. However, I may be misunderstanding the way to go about it.

I first added the CompletedSystemColumn (Modified Date) to my sheet and the "helper" column with unique data, then created the "Completed" sheet and a workflow that copies a row to that sheet when "Done" is checked.

When I tried getting the "Completed" sheet's CompletedSystemColumn value, I get errors. So I added a "Date" column with:

=IFERROR(DATEONLY(CompletedSystemColumn@row), "")

When "Done" is checked in the first sheet, the row successfully copies, and the "Completed" sheet's "Date" column has the date. A "Completed Date" in the first sheet has:

=IF(Done@row = 1, INDEX({CompletedDate9}, MATCH(AutoNumber@row, {AutoNumber3})), "")

This all appears to be working fine, as long as "Done" boxes are checked one at a time. However, the first sheet also has nested sub-tasks, where if "NA" is checked at the parent level, the decendents' "NA" and "DONE" boxes are automatically checked. When this happens, all the rows are copied, but the formula in the "Completed" sheet's "Date" column is broken, and the entire functionality is lost for all subsequent instances of "Done" being checked in the first sheet.

First, have I totally gone off the mark with how this was intended?

Second, will I need to abandon the auto-checking of child checkboxes to make this work consistently?

Tori Green

I'm struggling with this as well. Our current project plans rely on self reporting for the actual completion date so we can compare it to the intiial deadline for metrics of what we overdue and how to continue to build out more accurate plans in the future. I tried building a second sheet where tasks are copied once they are marked as complete (they are marked complete using a status bar/symbol column type). However, the formula is not able to return any matching values.

=VLOOKUP([Task Name]@row, {Tori Completed Tasks SEEK21 Timeline Range 2}, 1)

Andrée Starå

Hi @Tori Green

Have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

MarieEve

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action?_ga=2.55574114.839407798.1617699064-1916390700.1572860636&_gl=1*1h9adm5*_ga*NTg4MDIzOTI1LjE2NjYzNzI1MjQ.*_ga_ZYH7XNXMZK*MTY2NzM5MTkwMi4zLjEuMTY2NzM5MjI0My41MC4wLjA.