具有自动更新到期日的每周重复任务

amcclean
amcclean
编辑12/12/22 公式和函数

你好,


我想设立一个重复的每周任务。

  • 完成后,它会将行状态从“done”重置为“ready to work”。
  • 当状态重置为“准备工作”时,截止日期将自动提前一周。
  • 拥有的好处:我从一开始就知道,几个月后这个任务根本不会发生。我是否可以将任务安排为每周运行一次,从3月1日到6月15日,暂停并重新启动,从8月15日到10月31日,等等?

谢谢

答案

  • 嗨amcclean,

    如果在重复的任务中,你打算自动填充同一行,我不认为有一种自动的方式来实现你所尝试的。但是,如果需要在工作表中填写相关任务,则可以设置一个基于时间的每周发送的工作流更新请求当一个特定的条件满足:在这种情况下,条件可能是'Status'中的值下拉列是“准备工作”。

    注意,当设置一个基于时间的工作流时,你也可以选择设置一个“结束日期”,在那里工作流应该工作到。但是一旦它停止,你需要手动重置它,因为没有自动重新启动它的方法。

    你也可以添加一个要更新的附加动作块更新请求完成后,将“状态”列改为“完成”(您可能需要添加额外的条件,以避免在发送更新请求但未完成时状态发生变化。下面的例子展示了这个工作流的样子:

    截图2022-12-15 at 09.56.12.png
    截图2022-12-15 at 09.56.41.png
    截图2022-12-15 at 09.56.54.png


    我希望这能对你有所帮助。

    干杯!

    胡里奥

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
[Date of Site Survey (Product)]@row, \"yes\", \" \")) Intended logic\/result - If date cell is blank, then \"blank\" (this worked when I tried without the 2nd argument added) If date is in…","categoryID":322,"dateInserted":"2023-06-09T14:00:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T14:29:50+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:40:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162220,"lastUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:40:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3372641415,"url":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","format":"Rich","lastPost":{"discussionID":106265,"commentID":379837,"name":"Re: If Date is blank + past formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379837#Comment_379837","dateInserted":"2023-06-09T14:29:50+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:40:13+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-06-09T14:28:35+00:00","dateAnswered":"2023-06-09T14:12:11+00:00","acceptedAnswers":[{"commentID":379830,"body":"

Try this:<\/p>

=IF([Date of Site Survey (Product)]@row <> \"//www.santa-greenland.com/community/discussion/98590/\", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \"no\"))<\/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":106249,"type":"question","name":"Modifying a date based on 2 variables","excerpt":"Hello Everyone! I would like to create a formula that says If the value in the \"Variable\" column is \"HIGH\" AND the date in \"Column 1\" has a year less than today's year, return that date with TODAY\"S year. Otherwise return that date unchanged. In other words, dates in the previous years are brought to this year, dates in…","categoryID":322,"dateInserted":"2023-06-08T23:28:09+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T15:45:32+00:00","insertUserID":127672,"insertUser":{"userID":127672,"name":"Carroll Wall","url":"https:\/\/community.smartsheet.com\/profile\/Carroll%20Wall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:44:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-09T15:55:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":45,"score":null,"hot":3372597821,"url":"https:\/\/community.smartsheet.com\/discussion\/106249\/modifying-a-date-based-on-2-variables","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106249\/modifying-a-date-based-on-2-variables","format":"Rich","tagIDs":[216,234,249,254],"lastPost":{"discussionID":106249,"commentID":379864,"name":"Re: Modifying a date based on 2 variables","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379864#Comment_379864","dateInserted":"2023-06-09T15:45:32+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-09T15:55:57+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/3EAGX5UGDNCQ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T15:44:08+00:00","dateAnswered":"2023-06-09T15:30:15+00:00","acceptedAnswers":[{"commentID":379858,"body":"

Sure, here is how it breaks down:<\/p>

=IF([Variable]@row <> \"High\", \"//www.santa-greenland.com/community/discussion/98590/\"\n<\/pre>

<> is a way of saying \"not equal to\". So, in this case, we are saying \"Do the following thing if [Variable] is not equal to \"High\". The double quotes, \"//www.santa-greenland.com/community/discussion/98590/\", is another way of saying \"blank\". So if our formula sees that [Variable] contains anything other than \"High\", it is going to set [Goal Column] as blank.<\/p>

IF(YEAR(TODAY()) > YEAR([First Column]@row), DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)), [First Column]@row))\n<\/pre>

This section of the formula applies to the \"else\" part of the original IF statement. The first part looked for [Variable] not equal to \"High\", so this part will apply to [Variable] that does equal \"High\". First, we are asking whether today's year, YEAR(TODAY(), is greater than the year of [First Column], YEAR([First Column]). If it is, we are going to build a date using the present year, combined with the month and day from [First Column], DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)).<\/p>

[First Column]@row\n<\/pre>

Lastly, if the current year is not greater than [First Column], i.e., they are the same, then we are simply going to set [Goal Column] to the same value as [First Column].<\/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":216,"urlcode":"Non-profit","name":"Non-profit"},{"tagID":234,"urlcode":"Health-care","name":"Healthcare"},{"tagID":249,"urlcode":"Education","name":"Education"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106240,"type":"question","name":"How to make a manual Row ID based on conditions?","excerpt":"I'm not even sure if this is possible but I invite all the formula wizards to take a crack at it! I have this intake sheet with 10 rows, all that have a corresponding auto-generated Row ID from the system column. Row 1 is 23-001, row 2 is 23-002, row 3 is 23-003, etc. When a project gets approved, I want another column,…","categoryID":322,"dateInserted":"2023-06-08T20:55:41+00:00","dateUpdated":"2023-06-09T07:54:22+00:00","dateLastComment":"2023-06-09T16:11:17+00:00","insertUserID":155918,"insertUser":{"userID":155918,"name":"Gabriel Barrera","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Gabriel%20Barrera","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m4MvHwJdY3o!cpDsp75M-3o!0p-TgKs_b-h","dateLastActive":"2023-06-09T16:09:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-09T16:46:49+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":22,"score":null,"hot":3372587218,"url":"https:\/\/community.smartsheet.com\/discussion\/106240\/how-to-make-a-manual-row-id-based-on-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106240\/how-to-make-a-manual-row-id-based-on-conditions","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106240,"commentID":379878,"name":"Re: How to make a manual Row ID based on conditions?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379878#Comment_379878","dateInserted":"2023-06-09T16:11:17+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-09T16:46:49+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-06-09T16:09:16+00:00","dateAnswered":"2023-06-09T13:52:50+00:00","acceptedAnswers":[{"commentID":379821,"body":"

Hi @Gabriel Barrera<\/a> <\/p>

Here's an example formula that may be able to help you:<\/p>

=IF(Status@row <> \"Approved\", \"//www.santa-greenland.com/community/discussion/98590/\", INDEX([Row ID]:[Row ID], COUNTIF(Status$1:Status@row, \"Approved\")))<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>

This first says, if the Status is not<\/strong> approved, then return a blank cell. <\/p>

=IF(Status@row <> \"Approved\", \"//www.santa-greenland.com/community/discussion/98590/\",<\/p>

Otherwise, use the INDEX function to look into the Row ID column and bring back the row on the list that's the same number of \"Approved\" rows there are, specific to this current row. <\/p>

Notice that the COUNTIF function references the top row cell: Status$1 and then the range goes down to the current row cell: Status@row<\/p>

Because of this, you won't be able to turn it into a column formula. However you can drag-fill this down the column and then all new rows will auto-populate with the formula as long as new rows are added immediately under current rows. See: Use or Override Automatic Formatting and Formula Autofill<\/a><\/p>

Cheers,<\/p>

Genevieve<\/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"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势