计算一个班次的总工作时数
我正在尝试为我们公司的所有员工创建一个每周的工作时间表，这样我们就可以看到轮班的开始和结束，以及每个员工每天和每周的总工作时间。
我联系了Smartsheet团队，却发现Smartsheet目前不支持基于时间的公式(这是如此令人失望，因为我们希望能够用这个程序做的很多事情包括协调成本和时间，并提取每周工时信息，以适当地安排员工和部门的任务)。
我在社区里看了一下，看看这个问题的解决方案是什么，但我找不到什么有效的东西(或者我可能使用了我发现错误的信息)。
我附上了一个我试图在Smartsheet上创建的时间表的截图，它在Excel上是等效的。午餐时间的两栏通常是隐藏的。我把它们包括在内是为了这个目的，因为这些时间需要从总工作时间中减去，并且需要成为等式的一部分。
评论
-
保罗新来的 ✭✭✭✭✭✭Smartsheet中的时间计算非常棘手。午餐时间是如何计算的?它会永远是30分钟，还是可能是31分钟或29分钟或其他时间?
thinkspi.com
-
肖恩R ✭根据你的熟悉程度，你可以用军事时间，用这种方式写公式很容易。这样就不用用“:”来分隔小时和分钟，你就不用担心中午的问题了。这将是一个非常基本的计算方式，直到Smartsheet实现更好的时间公式。
-
保罗新来的 ✭✭✭✭✭✭
24小时的时间确实使它更容易，但我发现使用一个额外的辅助列(或两个或三个取决于你想保持你的公式有多短)使12小时到24小时的转换实际上非常简单。类似于
=IF(FIND("PM"，[时间列])@row)> 0， (VALUE(LEFT([时间列]))@row， FIND(":"，[时间列])@row)- 1) + 12) +“”+ RIGHT([时间栏]@row， 2)， LEFT([时间列]@row， FIND(":"，[时间列])@row)- 1)) + "" + RIGHT([时间栏]@row2))
这一次完成了所有的转换。我通常将小时和分钟分开，进行转换，然后从那里进行计算。在我看来，当你在做计算时，如果分钟加起来超过60(加上1个小时)，以及所有这些乱七八糟的东西，这让它更容易(在我看来)。
我之所以询问午休时间，是因为每次分解相同的数字要比运行另一个计算更容易(尽管它只会增加一两个辅助列)。
thinkspi.com
-
JulianaD ✭
午餐总是30分钟!
总小时的计算方式是Endtime-Startime - (lunchend-lunchstart)。
-
保罗新来的 ✭✭✭✭✭✭
-
JulianaD ✭
这种情况很少见，但有时确实会发生，这会使公式变得非常复杂吗?
-
保罗新来的 ✭✭✭✭✭✭
有可能，或者……
您可以输入两行，每一行代表一天。
星期一:晚上十点半至上午十二时
星期二:上午12点至6点半
这是一种选择吗?
thinkspi.com
帮助文章参考资料欧宝体育app官方888
类别
Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.<\/p>
<\/p>
Change<\/p>
COUNT(CHILDREN())<\/p>
to<\/p>
COUNTIFS(CHILDREN(), OR(@cell <> \"\", @cell = \"\"))<\/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"}]},{"discussionID":106335,"type":"question","name":"Match Learner by Location in a Training Tracker for Metric Sheet","excerpt":"I am missing something, but I think it is because I am not accustom to working with dates. I am creating a metric sheet for dashboard. I have a training tracker that populates a date by a learners name & demographics (row) with columns of different courses (date columns). I didn't think the formula would be difficult , but…","categoryID":322,"dateInserted":"2023-06-12T19:39:08+00:00","dateUpdated":null,"dateLastComment":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":150616,"lastUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3373200148,"url":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","format":"Rich","lastPost":{"discussionID":106335,"commentID":380182,"name":"Re: Match Learner by Location in a Training Tracker for Metric Sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380182#Comment_380182","dateInserted":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+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-12T20:23:36+00:00","dateAnswered":"2023-06-12T19:52:03+00:00","acceptedAnswers":[{"commentID":380176,"body":"
=COUNTIFS({Learner Location Range}, [Location]@row, {Course Completion Date}, ISDATE(@cell))<\/p>
If I understand your scenario correctly, this may work.<\/p>"},{"commentID":380182,"body":"
Awesome, <\/p>
Exactly it! I was not identifying the date@cell. <\/p>
Thank you so very much!<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":9974,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
