Formula for selecting checkbox will populate completion date

Diamond,

If I understand your post, you want:

1. click a checkbox for when the row item is completed.

2. fill in a date column with today's date.

and have the date be today's day for always.

Well, you could use a third party tool that can edit a row, but that might be overkill.

If you won't editing the row after it is complete, this would work:

1. add the Modified (Date) colum if you don't already have it displayed - it is one of the Auto-Number/System column types

2. In the column (Date type) that you want to have the date populated use this formula:

=IF([Checkbox_column]23, MIN(TODAY(), DATEONLY(Modified23)))

This is for row 23.

It looks at the "Checkbox_column" for a check.

If it finds one, it will take the older of today's date "TODAY()" and the last time the ROW was modified. When first checked, it will be whatever day the row was last saved, which might not be today. But, as soon as you save the sheet, the Modified field will update to now / today and the column will update to today's date.

Tomorrow when you look at the row, it will still show today's (now yesterday's) date.

And it will until you change something else on the row and save the sheet again.

That's the closest I can get to a work-around without more sleep.

Not fool proof. 

What you are trying to do (I think) is autopopulate a cell until it is populated and then stop autopopulating it. Without an external tool, that won't work.

I believe.

Hope this helps.

Craig

Diamond,

I have been working on something similar and have yet to get it to work.

I do have one solution that will do what you want but it does not use a check box.

Change your checkbox column to a drop down and populate it with done or complete etc.

In the column you want to auto populate with a date keyed off of the completion column I used the following.

 =IF(Status44 = "Done", TODAY())

When I change the column to done it inputs the date in the column i have titled date completed.  If I change status back to anything else and re-activate a task it removes that date. When the sheet is saved and closed it is fine. When i re-open the original date is still there. 

I know this isn’t a check box like you requested but hopefully this logic can lead to your solution or maybe you have some flexibility in what you can setup.

Hope this helps.

Has anyone found an alternate solution?

Using Modified Date is not a viable option because I cannot add a new column to the sheet or change the data on a row in ANY way (otherwise, I end up losing this time stamp).

 =IF(Status1 = 1, TODAY())

This formula will do what you are looking for, which is populate with today's date a column based on another column being checked or not checked.  The key here is that a checked cell's value is "1", and unchecked value is "0".  That's it.  Hope it helps.

Hi Craig,

This seems like a good workaround, however when I add this to my sheet, even though the created date says 28/11/17 5:50 AM  and modified says 28/11/17 7:25 AM, it returns a date the day before > 27/11/17 . any ideas why this might be?

My formula is for row 3 =IF([Property]3, MIN(TODAY(), (Modified3)))

My time zone is set correctly. 

Regards, 

Mel

Because this seems like it's a highly desired functionality improvement for quite some time, how can we move up the priority in Smartsheets backlog?