Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

New to Formulas

Schultis
Schultis ✭
edited 12/09/19 in Archived 2016 Posts

Please give me formula for:

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

 

 

· Share on FacebookShare on Twitter

Comments

  • Jenny Lam
    Jenny Lam ✭
    edited 03/26/16

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

    · Share on FacebookShare on Twitter
  • Thomas Muscarello
    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.


     

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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. 

    · Share on FacebookShare on Twitter
  • Thomas Muscarello
    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.

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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! 

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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?

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    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

    · Share on FacebookShare on Twitter
  • Thomas Muscarello
    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.

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    Thanks for the update Thomas! 

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories