下一个工作天计算
背景:我有一个智能表，给出一个敏捷软件的发布编号，以Year. week .SubVer的形式计算发布日期，基于一年的第一个星期一(SubVer =一周的一天，1=星期一，2=星期二)，所以2015.18.1 = 4/27/2015(和2015.18.2 = 4/28/2015等)
我想做的是能够以某种方式将NetWorkDays(或类似的东西)纳入此计算中，例如Release 2015.22.1实际上落在5/26/2015(星期二)，因为周一5/25是联邦假日(阵亡将士纪念日)。
无论好坏，这基本上是我使用的主要计算(_SubVer是一个隐藏的列，我目前使用，直到我把这一切清理成一个更大的公式):
=IF(ISBLANK([Release Number]1)， ""， (VALUE(MID([Release Number]1, 6, 2)) * 7) + (_subversion - 1) - 14)
对于2015.18.1，我得到122，然后我将其添加到日期(2015.1,1,5)，得到4/27/2015。
什么好主意吗?
我担心的是，标准功能只是排除周六和周日，我唯一的选择是使用HolidayCellX引用，这意味着我必须将联邦假日嵌入到同一工作表中，因为上次我看，NetWorkDays()无法引用另一个工作表……
评论
-
特拉维斯 员工嗨，瑞秋，我试着自己解决这个问题，我的一个同事(一个公式专家)也看了这个问题，但我们还没能弄清楚如何将非工作日排除在外。我会继续考虑的，如果我想到什么，我会让你知道的!
类别
Like that it is working partially, because as I have sub assemblies, I will need to take the quantity of the PARENT() and multiply it to have the correct value.<\/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":106651,"type":"question","name":"Incorrect Argument Set with nested IF","excerpt":"Counting number of occurances where month\/year is 1\/23 AND where cell within the row does not contain \"Color Add\" =COUNTIFS({New Product Status (Imagen) Go Live Launch Date}, IFERROR(MONTH(@cell), 0) = 5, {New Product Status (Imagen) Go Live Launch Date}, IFERROR(YEAR(@cell), 0) = 2023, {New Product Status (Imagen) new or…","categoryID":322,"dateInserted":"2023-06-20T18:01:37+00:00","dateUpdated":"2023-06-20T19:20:48+00:00","dateLastComment":"2023-06-21T13:22:46+00:00","insertUserID":74386,"insertUser":{"userID":74386,"name":"Stephanie Kiecker","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20Kiecker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!W2nUX5neWjw!6djX-omKb7Q!x0GPDTlMr6q","dateLastActive":"2023-06-20T20:34:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":74386,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-21T13:21:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3374639663,"url":"https:\/\/community.smartsheet.com\/discussion\/106651\/incorrect-argument-set-with-nested-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106651\/incorrect-argument-set-with-nested-if","format":"Rich","lastPost":{"discussionID":106651,"commentID":381471,"name":"Re: Incorrect Argument Set with nested IF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381471#Comment_381471","dateInserted":"2023-06-21T13:22:46+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-21T13:21:43+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-21T11:33:23+00:00","dateAnswered":"2023-06-20T20:32:28+00:00","acceptedAnswers":[{"commentID":381387,"body":" The @cell part at the end is wrong. It will need to be @cell = \"Color Add\", but in doing that, you will need to adjust your NOT(CONTAINS part. Try replacing it with NOT(@cell = \"Color Add\")<\/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":106654,"type":"question","name":"I cannot get the jira connector to work","excerpt":"I have a business account. I am an Admin in Jira. There is nothing telling me how to connect. When I click on connect it tells me to contact SmartSheet. I had an account manager. Cant find his name. Can't seem to put in a support ticket either.","categoryID":343,"dateInserted":"2023-06-20T18:25:35+00:00","dateUpdated":"2023-06-21T11:24:59+00:00","dateLastComment":"2023-06-21T12:52:23+00:00","insertUserID":159164,"insertUser":{"userID":159164,"name":"clawson","url":"https:\/\/community.smartsheet.com\/profile\/clawson","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtd8k6WqAcj3CUkjC0jOTaQM0V6AiPZO2dYG7Ihh=s96-c","dateLastActive":"2023-06-21T12:52:51+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-21T13:21:25+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3374638078,"url":"https:\/\/community.smartsheet.com\/discussion\/106654\/i-cannot-get-the-jira-connector-to-work","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106654\/i-cannot-get-the-jira-connector-to-work","format":"Rich","lastPost":{"discussionID":106654,"commentID":381467,"name":"Re: I cannot get the jira connector to work","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381467#Comment_381467","dateInserted":"2023-06-21T12:52:23+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-21T13:21:25+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-21T12:54:58+00:00","dateAnswered":"2023-06-21T12:52:23+00:00","acceptedAnswers":[{"commentID":381467,"body":" Hi @clawson<\/a> <\/p> The Jira Connector would need to be purchased in addition to your Smartsheet plan. If your plan doesn't include this connector, you can contact the Sales team from this page. <\/a><\/p>=SUMIF([Part number]$185:[Part number]$369, CONTAINS([Part number]167, @cell), Quantity$185:Quantity$369)<\/code><\/strong><\/p>
