工作流将日期更新为TODAY()，直到另一个单元格= 100%
我在一列中有2个单元格:
- 哪个显示1-100之间的%
- 哪个应该显示日期
是否有可能使用工作流或其他自动化每天(在12:01am或任何其他时间)将日期字段更新为TODAY()，但一旦%字段= 100%就停止更新?
提前感谢!
最佳答案
-
Krissia B。 主持人谢谢你提供更多的细节。此时，当您不使用date列时，没有一个直接的方法来完成您试图完成的记录日期，但这是一个好主意!请提交改进请求在Smartsheet的未来发展中考虑到这一点。
同时，另一种解决方案是创建一个可以隐藏的辅助列(日期列)，然后将其转换为“项目结束日期”下的文本值。看看下面我的示例，看看它看起来是什么样子!
用于项目结束日期的公式。我使用了IFERROR函数，这样如果% Complete不是100%，它就不会给出错误消息。我用了"未完成"或者你也可以留空，这取决于你。
= IFERROR(如果(月([Proj1 -结束日期]@row) = 1,“1”,如果月([Proj1 -结束日期]@row) = 2,“2”,如果月([Proj1 -结束日期]@row) = 3,“3”,如果月([Proj1 -结束日期]@row) = 4,“四月”,如果月([Proj1 -结束日期]@row) = 5,“可能”,如果月([Proj1 -结束日期]@row) = 6,“6月”,如果月([Proj1 -结束日期]@row) = 7,“7”,如果月([Proj1 -结束日期]@row) = 8,“8”,如果月([Proj1 -结束日期]@row) = 9,“九月”,如果月([Proj1 -结束日期]@row) = 10,“十月”,IF(MONTH([Proj1 -结束日期]@row) = 11， "十一月"，IF(MONTH([Proj1 -结束日期]@row) = 12， "十二月"))))))))))))+ " " + DAY([Proj1 -结束日期]@row) + "， " + YEAR([Proj1 -结束日期]@row)， "未完成"
然后我使用了隐藏助手列(日期列)的公式作为参考。
=IF([Proj1]5 = 1, TODAY())
希望这对你有帮助!
欢呼~
Krissia
答案
-
Devin李 ✭✭✭✭✭
在字段中设置公式=Today()，然后在达到100%时自动记录日期。仅供参考，日期记录不适合依赖项。更多信息可以在下面的链接中找到。
https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action
-
谢谢-我的问题有点不同，但可能是因为我设置了我的表格有点奇怪。没有日期列-表格被用来显示不同的里程碑从其他项目拉进来，如下所示。因此，每个项目/外部表都是一个列，以便进行其他计算(Workstream1的公式是完成子任务/子任务总数的计数)。这适用于所有单元格，除了单元格“项目结束日期”，我想设置为今天()，直到“%完成”=100%。但因为列是Proj1，并不是一个日期列(它有日期，数字，文本)，我不确定你建议的工作流程将工作。有可能实现我试图用这个设置做的事情吗?如果没有，我可以继续手动放入项目结束日期，因为工作表的其余部分工作。
-
Krissia B。 主持人
谢谢你提供更多的细节。此时，当您不使用date列时，没有一个直接的方法来完成您试图完成的记录日期，但这是一个好主意!请提交改进请求在Smartsheet的未来发展中考虑到这一点。
同时，另一种解决方案是创建一个可以隐藏的辅助列(日期列)，然后将其转换为“项目结束日期”下的文本值。看看下面我的示例，看看它看起来是什么样子!
用于项目结束日期的公式。我使用了IFERROR函数，这样如果% Complete不是100%，它就不会给出错误消息。我用了"未完成"或者你也可以留空，这取决于你。
= IFERROR(如果(月([Proj1 -结束日期]@row) = 1,“1”,如果月([Proj1 -结束日期]@row) = 2,“2”,如果月([Proj1 -结束日期]@row) = 3,“3”,如果月([Proj1 -结束日期]@row) = 4,“四月”,如果月([Proj1 -结束日期]@row) = 5,“可能”,如果月([Proj1 -结束日期]@row) = 6,“6月”,如果月([Proj1 -结束日期]@row) = 7,“7”,如果月([Proj1 -结束日期]@row) = 8,“8”,如果月([Proj1 -结束日期]@row) = 9,“九月”,如果月([Proj1 -结束日期]@row) = 10,“十月”,IF(MONTH([Proj1 -结束日期]@row) = 11， "十一月"，IF(MONTH([Proj1 -结束日期]@row) = 12， "十二月"))))))))))))+ " " + DAY([Proj1 -结束日期]@row) + "， " + YEAR([Proj1 -结束日期]@row)， "未完成"
然后我使用了隐藏助手列(日期列)的公式作为参考。
=IF([Proj1]5 = 1, TODAY())
希望这对你有帮助!
欢呼~
Krissia
-
谢谢你！我会的!
帮助文章参考资料欧宝体育app官方888
类别
What about this:<\/p>
=IFERROR(ROUND((COUNTIFS(CHILDREN(), OR(@cell = \"Pass\", @cell = \"Fail-4Front Review\", @cell = \"Fail-Shorr Review\", @cell = \"Re-Test Ready\")) \/ COUNTIFS(CHILDREN(), @cell <> \"//www.santa-greenland.com/community/discussion/comment/\")) * 100) + \"%\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/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":[]},{"discussionID":105740,"type":"question","name":"Formula to count number of items if it matches both headings.","excerpt":"Hello, I am currently stuck on this formula. I'm not sure what I'm doing wrong but the formula keeps on providing a return of \"1\" for everything, when I know I have \"2\" counts of items which are under category 1 and have a status of in progress. =COUNT(COLLECT({Status}, $Label@row, {Project Category}, [Category 1]$1)) Any…","categoryID":322,"dateInserted":"2023-05-26T17:10:14+00:00","dateUpdated":null,"dateLastComment":"2023-05-26T20:18:53+00:00","insertUserID":160666,"insertUser":{"userID":160666,"name":"Joyce W","title":"Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Joyce%20W","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZYDq2b_lcvo!7rEq-_P-XHo!ATPah0gLcYz","dateLastActive":"2023-05-26T20:19:01+00:00","banned":0,"punished":0,"private":true,"label":"✭✭"},"updateUserID":null,"lastUserID":160666,"lastUser":{"userID":160666,"name":"Joyce W","title":"Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Joyce%20W","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZYDq2b_lcvo!7rEq-_P-XHo!ATPah0gLcYz","dateLastActive":"2023-05-26T20:19:01+00:00","banned":0,"punished":0,"private":true,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":43,"score":null,"hot":3370255147,"url":"https:\/\/community.smartsheet.com\/discussion\/105740\/formula-to-count-number-of-items-if-it-matches-both-headings","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105740\/formula-to-count-number-of-items-if-it-matches-both-headings","format":"Rich","lastPost":{"discussionID":105740,"commentID":377789,"name":"Re: Formula to count number of items if it matches both headings.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/377789#Comment_377789","dateInserted":"2023-05-26T20:18:53+00:00","insertUserID":160666,"insertUser":{"userID":160666,"name":"Joyce W","title":"Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Joyce%20W","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZYDq2b_lcvo!7rEq-_P-XHo!ATPah0gLcYz","dateLastActive":"2023-05-26T20:19:01+00:00","banned":0,"punished":0,"private":true,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/S9FSNBMUTXYC\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-05-26T20:18:59+00:00","dateAnswered":"2023-05-26T18:31:55+00:00","acceptedAnswers":[{"commentID":377757,"body":"
Not sure you need \"COLLECT\" here (or the fixed reference to the Label column). <\/p>
Try this instead: =COUNTIFS({Status}, Label@row, {Project Category}, [Category 1]$1)<\/p>
I created some sample data (including a cross-sheet), and this worked.<\/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":[]},{"discussionID":105743,"type":"question","name":"How do I create an if blank then this and if populated then that statement?","excerpt":"Hello! Below is my current formula for a column to calculate days behind. =[Planned Start Date]@row - TODAY() The problem is we want this sheet to be accurate without having to have cells have different formulas. What I would like it to calculate is IF [Actual Start Date]@row is populated, then [Planned Start Date]@row -…","categoryID":322,"dateInserted":"2023-05-26T18:24:49+00:00","dateUpdated":null,"dateLastComment":"2023-05-26T18:47:05+00:00","insertUserID":161837,"insertUser":{"userID":161837,"name":"AMcElfresh","url":"https:\/\/community.smartsheet.com\/profile\/AMcElfresh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-05-26T19:50:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-05-28T15:15:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":94,"score":null,"hot":3370254114,"url":"https:\/\/community.smartsheet.com\/discussion\/105743\/how-do-i-create-an-if-blank-then-this-and-if-populated-then-that-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105743\/how-do-i-create-an-if-blank-then-this-and-if-populated-then-that-statement","format":"Rich","lastPost":{"discussionID":105743,"commentID":377764,"name":"Re: How do I create an if blank then this and if populated then that statement?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/377764#Comment_377764","dateInserted":"2023-05-26T18:47:05+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-05-28T15:15:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-05-26T18:42:42+00:00","dateAnswered":"2023-05-26T18:32:54+00:00","acceptedAnswers":[{"commentID":377758,"body":"
Hi @AMcElfresh<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =IF([Actual Start Date]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", [Planned Start Date]@row - [Actual Start Date]@row, [Planned Start Date]@row - TODAY())<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic weekend!<\/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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
![https:\/\/us.v-cdn.net\/6031209\/uploads\/LHAMUURP99CV\/image.png<\/a><\/p>https:\/\/help.smartsheet.com\/articles\/2481986-set-the-current-date-with-record-a-date-action<\/a><\/p>","bodyRaw":"[{\"insert\":\"Set the formula to =Today() in the fields and then make an automation to record a date when it reaches 100%. Just an FYI the record a date doesn't work well with dependencies. More info can be found in the link below. \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/LHAMUURP99CV\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/LHAMUURP99CV\\\/image.png\"}},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481986-set-the-current-date-with-record-a-date-action\"},\"insert\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481986-set-the-current-date-with-record-a-date-action\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2021-11-30T00:16:33+00:00","insertUser":{"userID":141976,"name":"Devin Lee","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Devin%20Lee","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wuDav0gnudQ!HYnb31wbkr4!hoPeVxbhNwX","dateLastActive":"2023-05-10T18:32:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/312113#Comment_312113","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/312113#Comment_312113](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/LHAMUURP99CV\/image.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/185P3PTMIV1F\/formula.gif<\/a><\/p>Formula used for the Project End Date. I used an IFERROR function so that it doesnt give an error message if the % Complete is not 100%. I used something like "Not Completed" or you can leave it blank, its up to you.<\/p>=IFERROR(IF(MONTH([Proj1 - End Date]@row) = 1, "January", IF(MONTH([Proj1 - End Date]@row) = 2, "February", IF(MONTH([Proj1 - End Date]@row) = 3, "March", IF(MONTH([Proj1 - End Date]@row) = 4, "April", IF(MONTH([Proj1 - End Date]@row) = 5, "May", IF(MONTH([Proj1 - End Date]@row) = 6, "June", IF(MONTH([Proj1 - End Date]@row) = 7, "July", IF(MONTH([Proj1 - End Date]@row) = 8, "August", IF(MONTH([Proj1 - End Date]@row) = 9, "September", IF(MONTH([Proj1 - End Date]@row) = 10, "October", IF(MONTH([Proj1 - End Date]@row) = 11, "November", IF(MONTH([Proj1 - End Date]@row) = 12, "December")))))))))))) + " " + DAY([Proj1 - End Date]@row) + ", " + YEAR([Proj1 - End Date]@row), "Not Completed")<\/p>Then i used the formula for the hidden helper column (date column) as the reference.<\/p>=IF([Proj1]5 = 1, TODAY())<\/p>Hope this helps!<\/p>Cheers~<\/p>Krissia<\/p>","bodyRaw":"[{\"insert\":\"Hello \"},{\"insert\":{\"mention\":{\"name\":\"BrianWong\",\"userID\":142165}}},{\"insert\":\" \\nThank you for posting additional details. At this time, there isn't a direct to do what you are trying to accomplish to record a date when you are not using a DATE column, but it is a great idea! Please submit an \"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/form\\\/739aa75f30ca43a8a22eb53e4da7d409?_ga=2.83182933.2102510910.1638546920-895869768.1638546920\"},\"insert\":\"enhancement request\"},{\"insert\":\" to have this considered for future development in Smartsheet.\\n\\nIn the meantime, an alternate solution would be to create a helper column (date column) that you can hide then convert that into a text value under your \\\"Project End Date.\\\" See my sample below on how it would look like!\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/185P3PTMIV1F\\\/formula.gif\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/185P3PTMIV1F\\\/formula.gif\"}},{\"insert\":\"\\n\"},{\"insert\":\"Formula used for the Project End Date. I used an IFERROR function so that it doesnt give an error message if the % Complete is not 100%. I used something like \\\"Not Completed\\\" or you can leave it blank, its up to you.\\n=IFERROR(IF(MONTH([Proj1 - End Date]@row) = 1, \\\"January\\\", IF(MONTH([Proj1 - End Date]@row) = 2, \\\"February\\\", IF(MONTH([Proj1 - End Date]@row) = 3, \\\"March\\\", IF(MONTH([Proj1 - End Date]@row) = 4, \\\"April\\\", IF(MONTH([Proj1 - End Date]@row) = 5, \\\"May\\\", IF(MONTH([Proj1 - End Date]@row) = 6, \\\"June\\\", IF(MONTH([Proj1 - End Date]@row) = 7, \\\"July\\\", IF(MONTH([Proj1 - End Date]@row) = 8, \\\"August\\\", IF(MONTH([Proj1 - End Date]@row) = 9, \\\"September\\\", IF(MONTH([Proj1 - End Date]@row) = 10, \\\"October\\\", IF(MONTH([Proj1 - End Date]@row) = 11, \\\"November\\\", IF(MONTH([Proj1 - End Date]@row) = 12, \\\"December\\\")))))))))))) + \\\" \\\" + DAY([Proj1 - End Date]@row) + \\\", \\\" + YEAR([Proj1 - End Date]@row), \\\"Not Completed\\\")\\n\\nThen i used the formula for the hidden helper column (date column) as the reference.\\n=IF([Proj1]5 = 1, TODAY())\\n\\nHope this helps!\\nCheers~\\nKrissia\\n\"}]","format":"rich","dateInserted":"2021-12-03T17:48:52+00:00","insertUser":{"userID":116817,"name":"Krissia B.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Krissia%20B.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n9SCK0BN9D6E5.png","dateLastActive":"2023-05-26T20:31:10+00:00","banned":0,"punished":0,"private":false,"label":"Moderator"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/312495#Comment_312495","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/312495#Comment_312495](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/185P3PTMIV1F\/formula.gif\"){kind=link}