Formula for selecting checkbox will populate completion date

Diamond Dusang

Hello,

 

I've looked through different discussions to see if I can figure this out.

 

I'm trying to find a formula that will populate a cell with the date of when the checkbox in another cell has been selected.

 

In other words, when I check this box that I have completed this item then I want another cell to populate the date that I checked the box on.

J. Craig Williams

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

 

Richard Rymill SBP

Great explanation Craig, good workaround. 

Thomas Muscarello

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.

Travis

In regards to Thomas' post: https://community.smartsheet.com/discussion/new-formulas

Ramsay Zaki

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

J. Craig Williams

Ramsay,

I do this using Zapier for small numbers of sheets and the API for many.

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

Craig

Chamilla

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

J. Craig Williams

Chamilla,

The formula you provided will also update each day when the Sheet is viewed as the formulas do not toggle off again.

Craig

 

Melissa Erskine-Shaw

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

J. Craig Williams

There is a known issue (some might call it a bug) with the UTC time on the System columns (Modified and Created dates)

I move back and forth between Europe and USA and have yet to find a personal or account settings work-around to eliminate it.

Craig

 

Sean Taber

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?  

nsarode

EDIT:

Realized that using TODAY()  in formula for cells comes with a very big pitfall. The date in the cells get updated everytime the sheet is saved ! Smartsheet currently doesn't have a feature that will lock in a date into a cell based on a value in another cell. Selecting the date manually is the only foolproof way out.

=======================

I was in a similar need. This is the formula that worked for me. 

=IF([Done]4 = 1, TODAY(),"Pending")

In the above formula, it looked for check box in a column named "Done" (where 1 = ticked, 0 = unticked), and will add todays date if ticked, or simply insert "Pending" if it is not).

Note that if your column is made with "Restrict to dates only" option selected, then the "Pending" text will throw some warnings. But this is mainly to give you an idea of how to get this done. Also, the 4 is for the row number. But once the formula is created, you can simply drag the formula to apply to others in the same column.