时间计算公式

保罗新来的 · 03/04/22

@GarrettDyer这是一个只读HTML表，所以我看不到任何底层公式。

GarrettDyer · 03/04/22

@Paul新来的在这里是床单。我花了很多时间，我想我已经把所有的事情都安排好了。到目前为止，我不确定我对它有什么问题。除了最后两列，Smartsheet将时间更改为PST而不是CST，这是我当前的时区。

保罗新来的 · 03/04/22

@GarrettDyer我相信在这篇文章中可能会有一个解决方案，可以帮助您调整时区问题。

GarrettDyer · 03/04/22

@Paul新来的我看到了公式，问题是变化与PST不一致。如果我写的公式进行调整，时间是在CST，那么我将有计算的问题，只是反过来。

如果您检查“时间更改”列中的单元格历史记录，您将看到Smartsheet Automation正在进行更改并弄乱事情的事件。

Jimrny · 05/17/22

@Paul新来的

你能帮忙吗?如果可能的话，尝试让持续时间以hh:mm表示开始和停止之间的差异(以分钟为单位)?

杰夫瑞斯曼 · 05/17/22

@Jimrny看起来你已经有了。只需将公式中的“hrs”替换为“:”-

L_123 · 05/17/22

如果你想要它的“HH”部分，你需要一个带len的条件来放一个0。基本上就是公式的复制品

Jimrny · 05/17/22

@Jimrny<\/a> Looks like you've already got it. Just replace the " hrs " in your formula with ":" - <\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/PXCID720QBPG\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" Looks like you've already got it. Just replace the \\\" hrs \\\" in your formula with \\\":\\\" - \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/PXCID720QBPG\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/PXCID720QBPG\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\" \\n\"}]","format":"rich","dateInserted":"2022-05-17T15:13:17+00:00","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-06-09T17:20:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/329892#Comment_329892","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/329892#Comment_329892

我遇到的问题是我的持续时间似乎有点偏差。

不是59分钟，而是1小时59分钟?

我希望在持续时间下反映“XX小时XX分钟”。似乎是四舍五入，加上前一小时。

更新:60分钟后的时间看起来是正确的。是什么导致额外的一小时显示为59分钟?

L_123 · 05/17/22

尝试使用rounddown()而不是round

Jimrny · 05/18/22

谁能帮忙看看这个公式对拉分钟是否正确?出于某种原因，我不确定它计算的时间是否正确。

杰夫瑞斯曼 · 05/18/22

@Jimrny由于某种原因，当对负数使用MOD时，它会给出余数的错误部分。MOD(- 136,60)应该是16，但结果是44。MOD(- 148,60)得到32，而不是28。它们的共同点是，如果你用60减去MOD的计算，你会得到正确的分钟数。60-44 = 16,60 -32 = 28。

所以在你的公式中加入一个IF来包含60减去的计算(电子邮件保护)< 0:

=如果((电子邮件保护)< 0, round ()(电子邮件保护)/ 60,0) + ":" +(60 -国防部((电子邮件保护)， 60))，圆((电子邮件保护)/ 60,0) + ":" + mod ((电子邮件保护), 60))

Jimrny · 05/18/22

@Jimrny<\/a> For some reason, when using MOD on a negative number, it's giving you the wrong portion of the remainder. MOD(-136, 60) should be 16, but it gives you 44. MOD(-148, 60) gives you 32 instead of 28. What these have in common is that if you subtract MOD's calculation from 60, you get the right number of minutes. 60-44 = 16, 60-32 = 28. <\/p>

So add an IF to your formula to include that 60 minus calculation in when the Difference@row < 0 :<\/p>

=IF(Difference@row < 0, ROUND(Difference@row \/ 60, 0) + ":" + (60 - <\/strong>MOD(Difference@row, 60)), ROUND(Difference@row \/ 60, 0) + ":" + MOD(Difference@row, 60))<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" For some reason, when using MOD on a negative number, it's giving you the wrong portion of the remainder. MOD(-136, 60) should be 16, but it gives you 44. MOD(-148, 60) gives you 32 instead of 28. What these have in common is that if you subtract MOD's calculation from 60, you get the right number of minutes. 60-44 = 16, 60-32 = 28. \\nSo add an IF to your formula to include that 60 minus calculation in when the Difference@row < 0 :\\n=IF(Difference@row < 0, ROUND(Difference@row \\\/ 60, 0) + \\\":\\\" + \"},{\"attributes\":{\"bold\":true},\"insert\":\"(60 - \"},{\"insert\":\"MOD(Difference@row, 60)), ROUND(Difference@row \\\/ 60, 0) + \\\":\\\" + MOD(Difference@row, 60))\\n\\n\"}]","format":"rich","dateInserted":"2022-05-18T16:59:16+00:00","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-06-09T17:20:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330113#Comment_330113","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330113#Comment_330113

@Jeff瑞斯曼结果非常好!也许你能帮忙?出于某种原因，我认为任何超过30 - 59分钟的差异都会显示出额外的一个小时的持续时间。

保罗新来的 · 05/19/22

@Jimrny你试过吗?@L_123使用ROUND down代替ROUND的建议?

Jimrny · 05/20/22

@Jimrny<\/a> Did you try @L_123<\/a>'s suggestion of using ROUNDDOWN instead of ROUND?<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" Did you try \"},{\"insert\":{\"mention\":{\"name\":\"L_123\",\"userID\":16866}}},{\"insert\":\"'s suggestion of using ROUNDDOWN instead of ROUND?\\n\"}]","format":"rich","dateInserted":"2022-05-19T14:23:23+00:00","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-06-10T20:34:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330243#Comment_330243","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330243#Comment_330243

刚涂完，将在30、45和59分钟后测试和观察。

BillH · 07/12/22

@Paul新来的我已经通读了所有这10页(非常感谢你把这一切放在一起)，我仍然有问题。我使用移动行建议来获取请求完成时的日期时间戳。然后，我使用vlookup将完成的日期/时间戳获取到主工作表中。我试图计算TAT从创建到完成，但一个简单的完成创建将不起作用。大多数TAT将在同一天，但许多不会。我已经尝试了各种其他的解决方案，像一个提取日期在一个字段和时间在另一个为每个创建和完成日期/时间戳。这适用于创建的日期，但对于我从vlookup中获得的日期，我得到一个“无效数据类型”。我在Completed vlookup公式的末尾添加了+“”，以获得填充时间。对我做错了什么有什么想法吗?非常感谢你的帮助。

时间计算公式

评论

类别