计算从一列传递到另一列的时间
例如，我想计算从开始列'00:00'到结束列'17:00'传递的时间
这个“加班时间”应该是自动填写的，现在我必须自己填写。
最佳答案
-
杰夫瑞斯曼 ✭✭✭✭✭✭@Paolo费尔南德斯在Smartsheet的世界里，04:00是一个文本值，你不能用文本值做数学运算。为了自动计算加班时间，首先需要将文本值转换为数值。
问题:
时间是否总是完整的，从整点开始到整点结束?或者从04:21到09:06。例如呢?如果是部分时间和奇怪的开始/结束时间，这改变了复杂性-但它仍然是非常可行的。
有没有一天加班到第二天才结束的情况?比如，22:00 - 04:00?
如果加班是整点开始和结束，则公式相当简单。我们使用LEFT函数分离时间条目的小时部分，并使用VALUE函数获得数值，然后相减:
=VALUE(LEFT([加班结束]@row, 2)) - VALUE(LEFT([加班开始]@row, 2))
如果加班可以跨越几天，我们需要一个If和一个替代公式:
=IF(VALUE(LEFT([加班开始]@行，2))> VALUE(LEFT([加班开始]@行，2))，(VALUE(LEFT([加班开始]@行，2)))- 12 - VALUE(LEFT([加班结束]@行，2))，(VALUE(LEFT([加班结束]@行，2))- VALUE(LEFT([加班开始]@行，2))- VALUE(LEFT([加班结束]@行，2))- VALUE(LEFT([加班开始]@行，2)))))
如果开始时间的值大于结束时间的值，则从开始时间减去12，再减去结束时间;否则，只需从结束时间中减去开始时间。所以如果加班时间是22:00 - 04:00(晚上10点-凌晨4点)，那么你最终的工作时间是22 - 12 - 4 = 6小时。
现在处理部分小时数就更复杂了。要做到这一点，我们需要将整个时间值转换成带有小数的数字。为此，我们需要使用RIGHT函数来隔离分钟，并将分钟值(60分钟中的#)转换为100的部分。我们用分钟除以60来求。这部分的公式是:
=VALUE(RIGHT([超时结束]@row, 2)) / 60
然后我们需要将得到的小数加到小时部分:
=VALUE(LEFT([加班结束]@row, 2)) + (VALUE(RIGHT([加班结束]@row, 2)) / 60)
所以对于09:06，你最终会得到9.10。13:47，最后是13.783。
如果我们要走这条路，公式就会变得很长很混乱。因此，我建议添加两个隐藏的助手列要计算十进制值，OTStart和OTEnd。如果你在上面的IF公式中使用这些辅助列的值:
=如果(值(左(OTStart @row, 2)) >值(左(OTEnd @row, 2)),(值(左(OTStart @row, 2)) - 12 -值(左(OTEnd @row, 2))),(值(左(OTEnd @row, 2))——价值(左(OTStart @row, 2))))
有了以上所有的因素，从8:17到13:33的OT值将等于5.267。因此，我们仍然需要将其四舍五入到小数点后两位，然后通过乘以。6将小数值转换为分钟:
= INT (如果值(左(OTStart @row, 2)) >值(左(OTEnd @row, 2)),(值(左(OTStart @row, 2)) - 12 -值(左(OTEnd @row, 2))),(值(左(OTEnd @row, 2))——价值(左(OTStart @row, 2))))) + (round (value(右))如果值(左(OTStart @row, 2)) >值(左(OTEnd @row, 2)),(值(左(OTStart @row, 2)) - 12 -值(左(OTEnd @row, 2))),(值(左(OTEnd @row, 2))——价值(左(OTStart @row, 2))))， 3)， 2) * .6)
也就是说，取公式结果的整数部分，将其四舍五入到小数点后2位并乘以。6后，将其与小数部分相加。
检查括号的颜色，确保它们匹配!好运！
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
答案
-
杰夫瑞斯曼 ✭✭✭✭✭✭
@Paolo费尔南德斯在Smartsheet的世界里，04:00是一个文本值，你不能用文本值做数学运算。为了自动计算加班时间，首先需要将文本值转换为数值。
问题:
时间是否总是完整的，从整点开始到整点结束?或者从04:21到09:06。例如呢?如果是部分时间和奇怪的开始/结束时间，这改变了复杂性-但它仍然是非常可行的。
有没有一天加班到第二天才结束的情况?比如，22:00 - 04:00?
如果加班是整点开始和结束，则公式相当简单。我们使用LEFT函数分离时间条目的小时部分，并使用VALUE函数获得数值，然后相减:
=VALUE(LEFT([加班结束]@row, 2)) - VALUE(LEFT([加班开始]@row, 2))
如果加班可以跨越几天，我们需要一个If和一个替代公式:
=IF(VALUE(LEFT([加班开始]@行，2))> VALUE(LEFT([加班开始]@行，2))，(VALUE(LEFT([加班开始]@行，2)))- 12 - VALUE(LEFT([加班结束]@行，2))，(VALUE(LEFT([加班结束]@行，2))- VALUE(LEFT([加班开始]@行，2))- VALUE(LEFT([加班结束]@行，2))- VALUE(LEFT([加班开始]@行，2)))))
如果开始时间的值大于结束时间的值，则从开始时间减去12，再减去结束时间;否则，只需从结束时间中减去开始时间。所以如果加班时间是22:00 - 04:00(晚上10点-凌晨4点)，那么你最终的工作时间是22 - 12 - 4 = 6小时。
现在处理部分小时数就更复杂了。要做到这一点，我们需要将整个时间值转换成带有小数的数字。为此，我们需要使用RIGHT函数来隔离分钟，并将分钟值(60分钟中的#)转换为100的部分。我们用分钟除以60来求。这部分的公式是:
=VALUE(RIGHT([超时结束]@row, 2)) / 60
然后我们需要将得到的小数加到小时部分:
=VALUE(LEFT([加班结束]@row, 2)) + (VALUE(RIGHT([加班结束]@row, 2)) / 60)
所以对于09:06，你最终会得到9.10。13:47，最后是13.783。
如果我们要走这条路，公式就会变得很长很混乱。因此，我建议添加两个隐藏的助手列要计算十进制值，OTStart和OTEnd。如果你在上面的IF公式中使用这些辅助列的值:
=如果(值(左(OTStart @row, 2)) >值(左(OTEnd @row, 2)),(值(左(OTStart @row, 2)) - 12 -值(左(OTEnd @row, 2))),(值(左(OTEnd @row, 2))——价值(左(OTStart @row, 2))))
有了以上所有的因素，从8:17到13:33的OT值将等于5.267。因此，我们仍然需要将其四舍五入到小数点后两位，然后通过乘以。6将小数值转换为分钟:
= INT (如果值(左(OTStart @row, 2)) >值(左(OTEnd @row, 2)),(值(左(OTStart @row, 2)) - 12 -值(左(OTEnd @row, 2))),(值(左(OTEnd @row, 2))——价值(左(OTStart @row, 2))))) + (round (value(右))如果值(左(OTStart @row, 2)) >值(左(OTEnd @row, 2)),(值(左(OTStart @row, 2)) - 12 -值(左(OTEnd @row, 2))),(值(左(OTEnd @row, 2))——价值(左(OTStart @row, 2))))， 3)， 2) * .6)
也就是说，取公式结果的整数部分，将其四舍五入到小数点后2位并乘以。6后，将其与小数部分相加。
检查括号的颜色，确保它们匹配!好运！
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
-
保罗·费尔南德斯 ✭早上好，杰夫。非常感谢你的帮助，它确实帮助了我!我用了你的第一个公式，它解决了我的问题!
帮助文章参考资料欧宝体育app官方888
类别
=JOIN(COLLECT([Dash Columns]12:[Dash Columns]61, [Dash Columns]12:[Dash Columns]61, @cell <> \"//www.santa-greenland.com/community/discussion/101116/\"), \", \")<\/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":106627,"type":"question","name":"Automated due date for every Monday","excerpt":"I have several reports that I run. I have a SmartSheet that lists how often it runs (Daily, weekly or monthly). I have an equation that calculates the next due date based on the completion date. The completion date is automatically recorded when I complete the report. It works beautifully except for the times that weekly…","categoryID":322,"dateInserted":"2023-06-20T12:08:03+00:00","dateUpdated":null,"dateLastComment":"2023-06-20T13:15:59+00:00","insertUserID":120783,"insertUser":{"userID":120783,"name":"Lisa Vercellone","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Vercellone","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T16:08:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":120783,"lastUser":{"userID":120783,"name":"Lisa Vercellone","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Vercellone","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T16:08:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3374531042,"url":"https:\/\/community.smartsheet.com\/discussion\/106627\/automated-due-date-for-every-monday","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106627\/automated-due-date-for-every-monday","format":"Rich","lastPost":{"discussionID":106627,"commentID":381284,"name":"Re: Automated due date for every Monday","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381284#Comment_381284","dateInserted":"2023-06-20T13:15:59+00:00","insertUserID":120783,"insertUser":{"userID":120783,"name":"Lisa Vercellone","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Vercellone","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-20T16:08:37+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\/7LJZYGRS3588\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-20T13:17:21+00:00","dateAnswered":"2023-06-20T12:22:38+00:00","acceptedAnswers":[{"commentID":381263,"body":"
Incorporate the below into your formula it will give you the Monday following the last completed date.<\/p>
=[Completed Date]@row - (WEEKDAY([Completed Date]@row) - 2)+7<\/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":106620,"type":"question","name":"Need empty column blank","excerpt":"I want to use a formula like this below but if there is no date in the other column to add +14 to, then leave row blank. =([CONFIRM OW PHOTO DUE]@row + 14) Need the \"14\" left blank","categoryID":322,"dateInserted":"2023-06-20T00:19:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-20T14:11:32+00:00","insertUserID":162246,"insertUser":{"userID":162246,"name":"Shawn_K2","url":"https:\/\/community.smartsheet.com\/profile\/Shawn_K2","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nPFlXyflhuI!uj8vZ9JIm_o!1axowkoO9t8","dateLastActive":"2023-06-20T14:32:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-20T15:01:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":62,"score":null,"hot":3374494238,"url":"https:\/\/community.smartsheet.com\/discussion\/106620\/need-empty-column-blank","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106620\/need-empty-column-blank","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106620,"commentID":381293,"name":"Re: Need empty column blank","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381293#Comment_381293","dateInserted":"2023-06-20T14:11:32+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-20T15:01:55+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\/BW38GVCJFDPD\/2023-06-19-20-16-43-creative-ops-comprehensive-content-tracker-smartsheet-com.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"2023-06-19 20_16_43-Creative Ops - Comprehensive Content Tracker - Smartsheet.com.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-20T13:11:28+00:00","dateAnswered":"2023-06-20T12:13:43+00:00","acceptedAnswers":[{"commentID":381259,"body":"
=If([Confirm OW Photo Due]@row=\"//www.santa-greenland.com/community/discussion/101116/\",\"//www.santa-greenland.com/community/discussion/101116/\",[Confirm OW Photo Due]@row+14)<\/p>"},{"commentID":381278,"body":"
