按营业时间计算时差的公式

黛比J
黛比J ✭✭✭✭✭
在公式和函数

@Paul新来的

我希望计算营业时间的时差，这是上午8点到下午5点，也不包括假期

下面是一个数据示例，没有中断。这是服务电话的时间。


谢谢你:)


image.png


· 在Facebook上分享 在Twitter上分享
«1

答案

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

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Here is a formula for that<\/p>
=IF(AND([Task1]@row = 0, [Task2]@row = 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0, IF(AND([Task1]@row <> 0, [Task2]@row = 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0.2, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row = 0, [Task4]@row = 0, [Task5]@row = 0), 0.4, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row = 0, [Task5]@row = 0), 0.6, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row <> 0, [Task5]@row = 0), 0.8, IF(AND([Task1]@row <> 0, [Task2]@row <> 0, [Task3]@row <> 0, [Task4]@row <> 0, [Task5]@row <> 0), 1, \"Task missed\"))))))\n<\/pre>
This would be the formula using your column names<\/p>
=IF(AND([Gate 1 (kick off)]@row = 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.2, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.4, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.6, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row = 0), 0.8, IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row <> 0), 1, \"Task missed\"))))))\n<\/pre>
=IF(AND([Gate 1 (kick off)]@row = 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0, <\/p>
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row = 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.2, <\/p>
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row = 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.4, <\/p>
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row = 0, [Gate 5 (Close Out)]@row = 0), 0.6, <\/p>
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row = 0), 0.8, <\/p>
IF(AND([Gate 1 (kick off)]@row <> 0, [Gate 2 (Review)]@row <> 0, [Gate 3 (Approval)]@row <> 0, [Gate 4 (Execution)]@row <> 0, [Gate 5 (Close Out)]@row <> 0), 1, \"Task missed\"))))))<\/p>"},{"commentID":359375,"body":"
Using only the checkbox columns, I created a formula that would give the percentages you requested and would give a \"Task Missed\" for any time when not done sequentially. You would replace the numbers with the titles of your columns.<\/p>
=IF(AND([2]@row = 1, [1]@row = 0), \"Task Missed\", IF(AND([3]@row = 1, OR([1]@row = 0, [2]@row = 0)), \"Task Missed\", IF(AND([4]@row = 1, OR([1]@row = 0, [2]@row = 0, [3]@row = 0)), \"Task Missed\", IF(AND([5]@row = 1, OR([1]@row = 0, [2]@row = 0, [4]@row = 0, [4]@row = 0)), \"Task Missed\", IF(AND([1]@row = 0, [2]@row = 0, [3]@row = 0, [4]@row = 0, [5]@row = 0), 0, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1, [4]@row = 1, [5]@row = 1), 1, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1, [4]@row = 1), 0.8, IF(AND([1]@row = 1, [2]@row = 1, [3]@row = 1), 0.6, IF(AND([1]@row = 1, [2]@row = 1), 0.4, IF([1]@row = 1, 0.2))))))))))<\/p>
\n    
\n        \n            \"image.png\"<\/img><\/a>\n    <\/div>\n<\/div>\n

<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-30 17:49:31","updateUser":{"userID":129890,"name":"Matthew Bertucci","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20Bertucci","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hdFXYWcw9fo!pHlW6TNrojw!2z-ywAR-42d","dateLastActive":"2023-01-30T18:54:18+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":100365,"type":"question","name":"EDATE formula for End Date","excerpt":"I'm trying to find the correct EDATE replacement formula for finding the Contract End Date. I've been playing around for about 30 minutes and decided to ask here as I'm unable to find it myself. Activation Date + Term = Contract End Date ex. 2017-10-04 = 3 = 2020-10-04","categoryID":322,"dateInserted":"2023-01-30T16:08:36+00:00","dateUpdated":"2023-01-30T16:09:25+00:00","dateLastComment":"2023-01-30T17:08:32+00:00","insertUserID":157561,"insertUser":{"userID":157561,"name":"Kelly Campbell","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Campbell","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!XEjbX82-A1Y!7qla--X-XDo!78EI_xDz6E-","dateLastActive":"2023-01-30T17:05:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":157561,"lastUser":{"userID":157561,"name":"Kelly Campbell","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Campbell","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!XEjbX82-A1Y!7qla--X-XDo!78EI_xDz6E-","dateLastActive":"2023-01-30T17:05:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3350195228,"url":"https:\/\/community.smartsheet.com\/discussion\/100365\/edate-formula-for-end-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/100365\/edate-formula-for-end-date","format":"Rich","lastPost":{"discussionID":100365,"commentID":359355,"name":"Re: EDATE formula for End Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/359355#Comment_359355","dateInserted":"2023-01-30T17:08:32+00:00","insertUserID":157561,"insertUser":{"userID":157561,"name":"Kelly Campbell","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Campbell","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!XEjbX82-A1Y!7qla--X-XDo!78EI_xDz6E-","dateLastActive":"2023-01-30T17:05:30+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\/QMC2XI6MFCR5\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-01-30T17:07:51+00:00","dateAnswered":"2023-01-30T16:53:16+00:00","acceptedAnswers":[{"commentID":359346,"body":"
Kelly, Try this solution:<\/p>
=DATE(YEAR([Activation Date]@row) + [Term (Year)]@row, MONTH([Activation Date]@row), DAY([Activation Date]@row))<\/p>

<\/p>
\n    
\n        \n            \"sampl.jpg\"<\/img><\/a>\n    <\/div>\n<\/div>\n
I hope this helps!<\/p>

<\/p>
Michael<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-30 17:07:51","updateUser":{"userID":157561,"name":"Kelly Campbell","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Campbell","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!XEjbX82-A1Y!7qla--X-XDo!78EI_xDz6E-","dateLastActive":"2023-01-30T17:05:30+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":100367,"type":"question","name":"SUMIFS and CONTAINS Formula","excerpt":"I am try to sum the total weight of a column on a different sheet if the name contains certain text and will be released on a certain date. I am using the below formula but getting #INCORRECT =SUMIFS({Estimated Weights Range EST Weight}, CONTAINS(\"C-Vibez\", {Estimated Weights Strain Name}), {Estimated Weights Est Release…","categoryID":322,"dateInserted":"2023-01-30T16:17:10+00:00","dateUpdated":null,"dateLastComment":"2023-01-30T16:35:39+00:00","insertUserID":145691,"insertUser":{"userID":145691,"name":"Nate420","url":"https:\/\/community.smartsheet.com\/profile\/Nate420","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZN1fGUANZiQ!5ElfOfBnaTw!XyXkILiTufF","dateLastActive":"2023-01-30T16:40:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":86866,"lastUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2023-01-30T18:27:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3350192569,"url":"https:\/\/community.smartsheet.com\/discussion\/100367\/sumifs-and-contains-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/100367\/sumifs-and-contains-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":100367,"commentID":359340,"name":"Re: SUMIFS and CONTAINS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/359340#Comment_359340","dateInserted":"2023-01-30T16:35:39+00:00","insertUserID":86866,"insertUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2023-01-30T18:27:14+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-30T16:42:22+00:00","dateAnswered":"2023-01-30T16:35:39+00:00","acceptedAnswers":[{"commentID":359340,"body":"
I think you've got a SUMIFS syntax issue with the CONTAINS function. Try this:<\/p>
=SUMIFS({Estimated Weights Range EST Weight}, {Estimated Weights Strain Name}, CONTAINS(\"C-Vibez\", @cell)<\/strong>, {Estimated Weights Est Release Date}, IFERROR(MONTH(@cell), 0) = 2, {Estimated Weights Est Release Date}, IFERROR(YEAR(@cell), 0) = 2023) \/ 1000<\/p>
This satisfies the SUMIFS syntax, which is =SUMIFS(Range to Sum, Criteria Range 1, Criteria 1<\/strong>, Criteria Range 2, Criteria 2...)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-30 16:42:22","updateUser":{"userID":145691,"name":"Nate420","url":"https:\/\/community.smartsheet.com\/profile\/Nate420","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZN1fGUANZiQ!5ElfOfBnaTw!XyXkILiTufF","dateLastActive":"2023-01-30T16:40:18+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"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">