当项目设置为星期一-星期五时，生成周末日期
@Kerlyn摩特谢德我把你的问题放到另一个话题上了。
你的问题:
我有个问题，希望你能解决。
我的项目持续时间只有工作日，所以我这样设置我的项目设置。然而，我确实有一些任务只在周末发生，我的表格没有根据我的设置计算。您是否有一个公式，它将基于前一个是周末的日期而忽略工作日?我希望这是有道理的。
凯!
thinkspi.com
答案
-
保罗新来的 ✭✭✭✭✭✭@Kerlyn摩特谢德您是否在工作表上启用了依赖项?
如果是这样，您将需要调整项目设置中的工作日，关闭它们并使用公式复制它们的工作方式，或者使用第二个日期类型列来存放公式，但是这个第二列在依赖项设置中不可用。
thinkspi.com
-
谢谢!我将尝试使用公式复制设置。
-
保罗新来的 ✭✭✭✭✭✭
帮助文章参考资料欧宝体育app官方888
类别
=IF(OR(Status@row = \"Complete\", Status@row = \"Canceled\", AND(Status@row = \"In Progress\", End@row>= TODAY())), \"Green\", IF(OR(AND(Status@row = \"In Progress\", End@row<= TODAY(-2)), AND(Status@row = \"Not Started\", Start@row<= TODAY(-2))), \"Red\", \"Yellow\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 15:39:50","updateUser":{"userID":156761,"name":"Indiemusicboy","url":"https:\/\/community.smartsheet.com\/profile\/Indiemusicboy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-14T22:26:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":99683,"type":"question","name":"Formula with IF, AND, CONTAINS","excerpt":"I need a formula that generates a different price depending on which options have been selected in two different columns. In one column I'm using the word premium to differentiate between the options (whether or not the text contains \"premium\" - because there are other options in the dropdown that do not contain…","categoryID":322,"dateInserted":"2023-01-13T18:13:16+00:00","dateUpdated":null,"dateLastComment":"2023-01-13T21:55:08+00:00","insertUserID":156854,"insertUser":{"userID":156854,"name":"Alyce R.","url":"https:\/\/community.smartsheet.com\/profile\/Alyce%20R.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T22:37:29+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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":36,"score":null,"hot":3347286504,"url":"https:\/\/community.smartsheet.com\/discussion\/99683\/formula-with-if-and-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99683\/formula-with-if-and-contains","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":99683,"commentID":357172,"name":"Re: Formula with IF, AND, CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357172#Comment_357172","dateInserted":"2023-01-13T21:55:08+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-01-13T22:02:45+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-01-13T20:28:39+00:00","dateAnswered":"2023-01-13T19:36:51+00:00","acceptedAnswers":[{"commentID":357139,"body":"
Formulas don't always scale very well which is why I suggested a reference table. If you would prefer to work with a formula, then you would use something like this for the example in your original post:<\/p>
=IF(CONTAINS(\"Premium\", [Which newsletter?]@row), IF(CONTAINS(\"Spotlight\", Placement@row), \"1000\", \"900\"), IF(CONTAINS(\"Spotlight\", Placement@row), \"800\", \"700\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 20:28:39","updateUser":{"userID":156854,"name":"Alyce R.","url":"https:\/\/community.smartsheet.com\/profile\/Alyce%20R.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T22:37:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":99675,"type":"question","name":"Tracking the cumulative days of a cell value change","excerpt":"I need to create column where i can track a number of days a column is checked. in my sheet, there is a check box field for Active status and another column named Active days. when the active status is checked, then it should record the number of days it is checked in the active days column. when someone unchecks it, the…","categoryID":322,"dateInserted":"2023-01-13T15:52:45+00:00","dateUpdated":"2023-01-13T15:54:51+00:00","dateLastComment":"2023-01-13T21:49:23+00:00","insertUserID":120420,"insertUser":{"userID":120420,"name":"asish lamichhane","url":"https:\/\/community.smartsheet.com\/profile\/asish%20lamichhane","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:26:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":120420,"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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":32,"score":null,"hot":3347274728,"url":"https:\/\/community.smartsheet.com\/discussion\/99675\/tracking-the-cumulative-days-of-a-cell-value-change","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99675\/tracking-the-cumulative-days-of-a-cell-value-change","format":"Rich","lastPost":{"discussionID":99675,"commentID":357169,"name":"Re: Tracking the cumulative days of a cell value change","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357169#Comment_357169","dateInserted":"2023-01-13T21:49:23+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-01-13T22:02:45+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-01-13T20:26:13+00:00","dateAnswered":"2023-01-13T19:17:57+00:00","acceptedAnswers":[{"commentID":357132,"body":"
Sorry about that. I misunderstood the first time I read.<\/p>
<\/p>
You will need to set up the date checked column, and you will also need to set up a second sheet as a recipient for a copy row automation that copies when the box is unchecked. This will capture the static data on the second sheet for how many days the box was checked before it was again unchecked. You will also need a unique identifier on each row (Auto-Number column).<\/p>
Then the formula would be<\/p>
=SUMIFS({Static Sheet Days Checked Column}, {Static Sheet Unique ID Column}, @cell = [Unique ID]@row) + IF([Checkbox Column]@row = 1, TODAY() - [Checked Date]@row, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 20:26:11","updateUser":{"userID":120420,"name":"asish lamichhane","url":"https:\/\/community.smartsheet.com\/profile\/asish%20lamichhane","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:26:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"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":[]}">
