New to Formulas

Schultis

Please give me formula for:

When checkbox column (complete) is checked that the column (date completed) automatically changes to today date.

 

 

Jenny Lam

Hope this will help: https://community.smartsheet.com/discussion/formula-selecting-checkbox-will-populate-completion-date

Thomas Muscarello

 

 

 

I have been working on something similar and have yet to get it to work. I can get the 'date completed' to auto-populate once the check box is done but every time I re-open the sheet it update the date again.

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 'date completed' 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 on your sheet.

Hope this helps.

 

 

Travis

Thomas, just like the checkbox option, "TODAY" will update each day.

 

There are a couple options for this but it wont be exactly what you are looking for. 

 

Each cell has cell history which will show you all the changes made to the cell, when the change was made and by who. To view this, right click in the cell and select View History. This will show you a timestamp of when the box was checked. 

 

The other option is to use the Modified (Date) System column which will show a timestamp of the last time the row was modified. This would work if checking the box is the last change to occur in the row. 

Thomas Muscarello

Travis thanks for the reply. As of right now i am using the drop down and it is functioning as described above.  The check box method updates the date every day but the drop down is static unless you change the status again. If this is a bug please dont report and fix lol.

 

I did not know about the history option for each cell. That will work perfect for me and also reduce the size of my sheet.

 

Thanks for the reply.

Travis

Thomas - TODAY should update each day, even with a Dropdown list. I am going to test this in one of my sheets today and check back tomorrow to see the results.

 

I will let you know what I find! 

Travis

Thomas, when I opened my test sheet today, the TODAY() function changed the date to March 29th, which is expected behavior.

 

Are you finding the TODAY function is not changing for you each day?

J. Craig Williams

Thanks for beating me to the investigation Travis!

Thomas had updated a few threads with that and I hoped it didn't work.

That would be bad in so many places....

 

Craig

Thomas Muscarello

Just getting back to this.

 

I got one sheet that updated as you indicated i would soooooo with that being said my posts were wrong. im going to try and remove them so other dont waste time reviewing them. 

 

I have another sheet that did not but im not the owner of it there are several different formatting changes that occur off the same status field and im guessing it has to do with something else.

Travis

Thanks for the update Thomas!