Status Column Change to Record Date of Change

Hello,

我在我的公司为一组开发指标and need some help. They do not log when they mark something as complete by date just by status. The date of the status change is in the cell history but I was wondering if there was a way to write a formula to show when a status is set to completed it records the date in a separate column. Does anyone have any experience with a formula like this? I am not sure where to start.


Thank you!

Tim

Best Answer

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓

    Hi@tim.curtin

    The "Record a Date" feature is found within the Automation tab. First add a date column to your sheet and then go "Create Workflow from Template", scroll to the bottom and choose "Record the date when specificed criteria are met". It will explain what it does. From there the options are pretty straight forward. Let me know if you have any questions.

    Thanks,

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet PLATINUM Partner

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi @BristolCVN<\/a> <\/p>

I hope you're well and safe!<\/p>

You must add\/change the Rule #1 name.<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":443,"urlcode":"dynamic-view","name":"Dynamic View"}]},{"discussionID":108759,"type":"question","name":"Help with formula","excerpt":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the number of dropdown items from. This is the sheet that i want the formula written in to return an output. Currently I have =IF(HAS({centers},…","snippet":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the…","categoryID":322,"dateInserted":"2023-08-09T20:13:45+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T17:17:45+00:00","insertUserID":161673,"insertUser":{"userID":161673,"name":"Lauren Hughes","url":"https:\/\/community.smartsheet.com\/profile\/Lauren%20Hughes","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WSHqXkuSMHM!nddov_PnFbs!Fw3jLOwOmG0","dateLastActive":"2023-08-10T17:37:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3383301690,"url":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108759,"commentID":390080,"name":"Re: Help with formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390080#Comment_390080","dateInserted":"2023-08-10T17:17:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/CM7LO30OUR2Q\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T17:16:38+00:00","dateAnswered":"2023-08-09T21:23:23+00:00","acceptedAnswers":[{"commentID":389908,"body":"

Try this:<\/p>

=COUNTM(COLLECT({July}, {Centers}, HAS(@cell, Center@row)))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108756,"type":"question","name":"Showing what quarters students are here from date ranges","excerpt":"Hello - I am trying to build a report for my supervisor that shows what quarters students are here from two date ranges. My columns are Experience Start Date Experience End date Quarter - I have this formula in quarter currently that pulls the quarter from whatever month the experience start date begins in =\"Q\" +…","snippet":"Hello - I am trying to build a report for my supervisor that shows what quarters students are here from two date ranges. My columns are Experience Start Date Experience End date…","categoryID":322,"dateInserted":"2023-08-09T19:29:46+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:56:30+00:00","insertUserID":164879,"insertUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164879,"lastUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3383297176,"url":"https:\/\/community.smartsheet.com\/discussion\/108756\/showing-what-quarters-students-are-here-from-date-ranges","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108756\/showing-what-quarters-students-are-here-from-date-ranges","format":"Rich","lastPost":{"discussionID":108756,"commentID":390066,"name":"Re: Showing what quarters students are here from date ranges","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390066#Comment_390066","dateInserted":"2023-08-10T16:56:30+00:00","insertUserID":164879,"insertUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/MS2Q5K5FZJPC\/smartsheet-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Smartsheet.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T16:56:13+00:00","dateAnswered":"2023-08-09T21:34:54+00:00","acceptedAnswers":[{"commentID":389920,"body":"

Try this:<\/p>

=\"Q\" + ROUNDUP(MONTH([EXPERIENCE START DATE:]@row) \/ 3) + IF(ROUNDUP(MONTH([EXPERIENCE START DATE:]@row) \/ 3) <> ROUNDUP(MONTH([EXPERIENCE END DATE:]@row) \/ 3), \" - Q\" + ROUNDUP(MONTH([EXPERIENCE END DATE:]@row) \/ 3), \"//www.santa-greenland.com/community/discussion/108034/\")<\/p>

Hope that works for ya!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions