Trying to connect a check box column to a completion date column

Nate Parent

I am looking to connect a column in which I have a check box indicating done (check) and not done (unchecked).  On the same sheet I have a column for Completion Date.  I would like the sheet to auto-populate the Completion Date field each time a check box is checked as done.

 

Is that possible?

Janelle Cates

There may be other solutions, but I have found that using a hidden "Modified (Date)" column in combination with an IF formula gets the job done. If you want it to populate in your "Completion Date" column, use IF([Check Box]= 1, [Modified], "") . This only works if checking the box is the last thing you change though. 

Jeff K.

You would have to manually lock the row from additional edits, or else the Modified date will continue to go forward in time

 

 

Zack S

Hello Nate, 

 

Using the Modified Date column, and possibly an IF formula to populate the Completion Date field would work, but as Jeff mentioned, this process will only work if marking the task Complete is the last edit that is made on that row. If another change is made on that row it will cause the Modified Date field to change causing the Completion Date to change as well. 

 

If other changes will be made on the row there wouldn't be a way to accurately report when a specific cell has been changed. You can right click on a cell to see the date/timestamp on when the cell was edited, but you can't pull that information into another cell. I'll be sure to add a vote for a timestamp formula to our enhancement request list. 

 

-Zack

Patrick Lawler

Nate and group, 

 

This exact problem can be solved using Azuqua, an integration partner of Smartsheet. I work for Azuqua and the logging timestamp in a column based on a checkbox being marked (checked) is one of the many scenarios that we enable inside of Smartsheet.

 

 

Using Azuqua you can set up a monitor for a change to column and in your particular case check to make sure the checkbox has been marked and then confirming the checkbox has been marked a timestamp / date will be entered into the column you specify.

 

With this solution you do not need to worry about dates being overwritten, which is one of the concerns from this particular discussion.

 

If you want to learn more please visit our app listing at Smartsheet here:

https://www.smartsheet.com/apps/azuqua

 

Please feel free to follow up with any questions.

-Patrick

Mike1

I would also like this exact functionality.

Logan

Can I add another vote for this functionality. Would love to be able to check a box and have a formula that auto fills the date. Something like =IF([CB1] = 1,GETDATE(),"")

Steve-CCLLC

Another vote for this functionality. Thanks.

Lynette Usry

Another vote for this enhancement! Thanks!

Wanda T

Searching for the enhancement request for this feature - desired to trigger email notification of action required (using Reminders)

 

 

J. Craig Williams

I do this now using Zapier (www.zapier.com)

 

I have a Zap that watches for an empty date column and a completion check box - when the check box is checked by the user, today's date is entered into the date column.

Later, if someone makes another change elsewhere in the row, which triggers the Zap, it does not do anything because the date column is not empty.

 

 

Craig

Jim Nubiola

+1

Bruno

Same here! Would love it!!!!

J. Craig Williams

My solution using Zapier is posted here:

 

http://ronin-global.com/2016/12/11/smartsheet-fixed-date-upon-completion/

 

Craig

Bruno

Hi,

I am just in SmartSheet, no Zapier, no other thhings.

For the same problem as you ... I finally just do it the other way round.

I have the to columns as you (done and completion date).

I enter the completion date (just enter t is enough to enter todays date) and I have inserted a formula in the 'done' checkbox column (IF(completion date = "", 0,1)

That works 'good enough' :-)

Cheers

Bruno

Ben W

Hi Craig, I saw your comment (above) about a zap that watches for an empty date column on Smartsheet and then enters a date. Can you let me know how you managed to do this - I've been using Zapier but for some reason can only find triggers that are related to wholesale changes (new row/comment/attachment or updated row) as opposed to being able to narrow it down to one column.

It would be awesome if I can replicate what you have done.