时间计算公式

莱斯利Merlino · 07/21/21

完美! !非常感谢!!

保罗新来的 · 07/22/21

@Helen边缘这里是一个线程，我们使用hh:mm:ss时间。

Hello, I'm hoping someone might be able to help me with calculating time in Smartsheet. We work with production teams in broadcasting, as well as other non-production teams, so calculating time is becoming rather vital, as more and more departments are using Smartsheet for a varying number of use cases: people and resource management, pre\/post-production admin etc.<\/p>

An old colleague of mine actually managed to create a sheet that was able to calculate it down to frames per second (HH:MM:SS:fps), as he was a technical whizz. The workflow is you type in the start and end times of of when an item has been shown (first 2 columns), then in column 4 (Fr_In) it uses a formula:<\/p>

=IF([Time Code IN (in HH:MM:SS)]14 <> "", ((((VALUE(LEFT([Time Code IN (in HH:MM:SS)]14, 2)) * 90000)))) + (VALUE(RIGHT([Time Code IN (in HH:MM:SS)]14, 2)) + INT(VALUE(RIGHT(LEFT([Time Code IN (in HH:MM:SS)]14, 8), 2)) * 25)) + INT(VALUE(RIGHT(LEFT([Time Code IN (in HH:MM:SS)]14, 5), 2)) * 1500))<\/em><\/p>

to converts the first column's value into numerics. The example on the dark blue row is, in this instance, what 10 hours equates to 900,000 frames. <\/p>

An equivalent formula is then used to work out the frames per minute numerical value for the output in column 5 (Fr_Out):<\/p>

=IF([Time Code OUT (in HH:MM:SS)]14 <> "", ((((VALUE(LEFT([Time Code OUT (in HH:MM:SS)]14, 2)) * 90000)))) + (VALUE(RIGHT([Time Code OUT (in HH:MM:SS)]14, 2)) * 25) + INT(VALUE(RIGHT(LEFT([Time Code OUT (in HH:MM:SS)]14, 5), 2)) * 1500))<\/em><\/p>

Then the 2 figures are deducted in column 6 (Fr_Dur) to work out the difference in time duration in frames per second. My colleague then used some helper columns (the last 3 columns) to convert the value in column 6 into hours, minutes and seconds, in each associated column, using the following formulas respectively:<\/p>

=IF(([Fr_Dur]14 \/ 90000) < 10, "0" + (INT([Fr_Dur]14 \/ 90000)), (INT([Fr_Dur]14 \/ 90000))) + ":" <\/em><\/p>

=IF(INT(([Fr_Dur]14 - (INT([Fr_Dur]14 \/ 90000) * 90000)) \/ 1500) < 10, "0" + INT(([Fr_Dur]14 - (INT([Fr_Dur]14 \/ 90000) * 90000)) \/ 1500), INT(([Fr_Dur]14 - (INT([Fr_Dur]14 \/ 90000) * 90000)) \/ 1500)) + ":"<\/p>

=IF(INT(([Fr_Dur]14 - (INT([Fr_Dur]14 \/ 1500) * 1500)) \/ 25) < 10, "0" + INT(([Fr_Dur]14 - (INT([Fr_Dur]14 \/ 1500) * 1500)) \/ 25), INT(([Fr_Dur]14 - (INT([Fr_Dur]14 \/ 1500) * 1500)) \/ 25))<\/p>

<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/KDN0PT3TJID0\/screenshot-2021-05-14-at-17-22-57.png<\/a><\/p>

I'm in the process of trying to tweak his old sheet, as for this particular use case, the team doesn't need to collate the data as granular as frames but we still need it down to the second (HH:MM:SS).<\/p>

At first glance, the above might look like it's working the values out correctly but the last 2 rows, in fact, have slightly wrong duration values, by 3 and 2 seconds respectively. I appreciate this does sound pedantic, however, timings in broadcasting are essential!! <\/p>

I've looked up a number of solutions in the Community Pages and then been wracking my brains trying to apply these suggestions to my problem here but I can't seem to get them to work. I'm hoping someone might be able to assist in simplifying the solution than it is currently. <\/p>

但对你来说，有几个关键的区别。首先，您只需要从一列转换时间，而不是从开始列和结束列转换时间。在上面的解决方案中，总是有两个数字的小时，但我注意到在你的例子中只有一个。我们需要用这个公式:

=(VALUE(LEFT([Start Time]@row, 2)) * 3600) + (VALUE(MID([Start Time]@row, 4,2)) * 60) + VALUE(RIGHT([Start Time]@row, 2)))

并将其调整为:

=(VALUE(LEFT([起始时间]@row, FIND(":"，[起始时间]@row) - 1)) * 3600) + (VALUE(MID([起始时间]@row, FIND(":"，[起始时间]@row) + 1,2)) * 60) + VALUE(RIGHT([起始时间]@row, 2)))

一旦你把所有的时间都转换成数值，你就可以对这一列求平均值。然后，要将其转换回hh:mm:ss格式，您需要将链接解决方案中的三个独立列“hh:”、“mm:”和“ss:”，并将它们全部添加到单个单元格的单个字符串中。

=hh:公式+ mm:公式+ ss:公式

保罗新来的 · 07/22/21

@Leslie Merlino很乐意帮忙。️

海伦边缘 · 07/23/21

@Paul新来的非常感谢!我一定会尝试一下，让你知道!

Syed Muhafzal · 07/31/21

@Paul新来的

好一条线!当我在寻找一个时间公式的时候偶然发现了这个!这真是天赐良机!

非常感谢，保罗。

我有一个问题，我正在为我们的IT团队在Smartsheet上建立一个票务系统。我们的流程是通过表单接收票据并触发警报等。问题是，我们确实有一个SLA专栏，它将由IT经理根据问题进行分类。优先级1将是2H响应时间等。这意味着，当优先级1在上午10点收到票时，我们必须在中午12点之前作出回应。

我的想法是

票务开始时间:自动创建日期(收到表格时)

优先级:手动输入(P1、P2、P3等)

SLA:基于优先级2H、8H、24H的自动单元格值

建议响应时间:基于SLA的公式，从票务开始时间。

完成时间:手动输入(当IT人员解决问题时)

已实现SLA:基于建议响应时间到结束时间的公式时长。

当然，所有sla都必须在办公时间内，从上午8点到下午5点。这意味着如果我有一张周一下午4:30的P1票，它必须在周二(第二天)上午9:30 .....之前得到响应

我看过你发表的一些作品，我觉得需要创建一个“shift”列可能是最近的吗?然而，我想知道你是否有其他的例子，可能几乎是我上面面临的挑战。即使你能给我指出另一个线程有类似的问题，我真的很感激。

任何想法吗?

谢谢

赛义德

瑞安Jezierski · 08/02/21

嗨,保罗,

试图利用你的公式从创建的戳中提取时间，我得到的是一个小时，而不是分钟。

=价值(中期((电子邮件保护)， find (" "，(电子邮件保护)) + 1, find (":"，(电子邮件保护)) - (find (" "，(电子邮件保护)(1) + if (1) + if (1)(电子邮件保护)， find (" "，(电子邮件保护)) + 1, find (":"，(电子邮件保护)) - (find (" "，(电子邮件保护)(1)) <> 12, if (find (" p "，(电子邮件保护)) > 0, 0)， if (find (" a "，(电子邮件保护)) > 0， -12)

保罗新来的 · 08/02/21

@Ryan Jezierski要包含分钟数，您需要添加粗体部分:

=价值(中期((电子邮件保护)， find (" "，(电子邮件保护)) + 1, find (":"，(电子邮件保护)) - (find (" "，(电子邮件保护)(1) + if (1) + if (1)(电子邮件保护)， find (" "，(电子邮件保护)) + 1, find (":"，(电子邮件保护)) - (find (" "，(电子邮件保护)(1)) <> 12, if (find (" p "，(电子邮件保护)) > 0, 0)， if (find (" a "，(电子邮件保护)) > 0， -12)+“:”+ mid ((电子邮件保护)找到(”:“(电子邮件保护)) + 1, 2)

注意:这个公式应该输出24小时的时间，在小时和分钟之间有一列。

Razetto · 08/03/21

Screenshot.pdf

你好,

我试图开发的是一个作业调度器，这意味着我有第一个作业(第一行)的开始日期和开始时间，以及运行该特定作业的估计时间;我需要计算的是估计的完成日期和时间，这反过来将是开始日期和时间为以下工作。

它类似于不使用依赖关系和以小时为单位的持续时间的项目管理。我们使用24小时的格式，有些作业可能会运行几天，而机器则是24/7运行。

根据开始日期、开始时间和作业运行时间，可以使用什么公式来计算完成日期和完成时间?我试过修改这篇文章中发现的许多不同的公式，但都不能奏效。任何想法都欢迎!

西奥希尔 · 08/04/21

嗨@Paul新来的，

希望你能收到这条信息。

谢谢你提供的这个非常复杂的公式。我已经把它载入我的电子表格了。

问题是我不想把它四舍五入到hr。我需要计算雇员扫描进出之间的确切时间。

这就是我想要创造的。

每位员工将获得一张带有二维码的身份证，如下图所示

项目经理将使用他的手机扫描员工，并在他们离开现场时再次扫描。

这就是上面的表格。

因此，我的第一列是一个自动日期戳，一旦员工将他的ID卡扫描到智能表中，它就会被创建。

现在，我认为我必须创建另一个工作表，在一行3列中汇总Employee Name。计时，超时和计算之间的时间，以确定工作小时数。

如果你能给我一些建议和公式，我将不胜感激。

谢谢

瑞安Jezierski · 08/20/21

@Paul新来的谢谢你！这工作。我正在利用这段时间，在此基础上增加1个小时，并希望利用这段时间向操作员显示何时需要从仪表板上完成下一次每小时检查。为此，我试图将最新的检查拉到另一个工作表，以便我可以在仪表板上的小部件中引用最后一次检查。我已经尝试了各种max/collect和index/collect来做到这一点，但没有运气。任何建议吗?

保罗新来的 · 08/23/21

@Syed Muhafzal“需要创建一个移位列”的解决方案将是我已经为您提供的最接近的解决方案。还有另一种解决方案，包括如何解释午餐以及进入不同的约会。你要把这三个部分结合起来。

@Razetto您将需要寻找解决方案，以考虑从开始日期移动到不同的日期。

@Theo希尔请参阅我上次对Ryan Jezierski的回复，了解如何包括会议记录。至于合并单个员工的多个条目，有许多解决方案。我认为它很可能需要涉及助手列和INDEX/COLLECT公式。我所看到的大多数解决方案都不一定与时间相关，但有很多解决方案应该具有适应性。

@Ryan Jezierski我不太明白你的意思。为什么要尝试使用COLLECT函数?是否有多个“最后检查”?你能提供截图供参考吗?

凯利普拉特 · 08/25/21

非常感谢@Paul新来的！这真的很有帮助-虽然我遇到了一个问题，也许我在阅读这条长长的线索的过程中错过了一些东西:)

我试图做一个简单的计算工作时间(没有逻辑需要考虑休息或其他任何东西)。员工将在下拉菜单中选择他们在15分钟内“打卡”的时间。然后他们将从下拉菜单中选择“打卡下班”的时间。

我需要计算一下小时数。我试图使用原始帖子中的公式转换为24小时，因为你说这样更容易计算，但是当我使用这个公式时，它只放入小时，而不是从时间表中输入的分钟。

将感谢您的输入我在这里错过了什么!如果对你有帮助的话，我很乐意和你分享一张表格。

=VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) + IF(CONTAINS("p"，[时间列]@row)， IF(VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) <> 12,12)， IF(VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) = 12， -12)))

保罗新来的 · 08/25/21

@Kelly普拉特你需要这样的东西……

=VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) + IF(CONTAINS("p"，[时间列]@row)， IF(VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) <> 12,12)， IF(VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) = 12， -12)))+ (VALUE(RIGHT([Time Column]@row, 2)) / 60)

凯利普拉特 · 08/25/21

谢谢@Paul新来的！这不是很有效，也许我没有解释清楚。

在这里，我发布了一个表格来展示我是如何设置它的一个例子。https://app.smartsheet.com/b/publish?EQBCT=ee75840a7a44466a981e93d12187effd

Time In, Time Out是用户从下拉菜单中选择的。

Time In转换，Time Out转换是将其转换为可以计算持续时间的格式。

保罗新来的 · 08/25/21

@Kelly普拉特对不起。我没意识到上午和下午的区别。试试这个吧。

=VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) + IF(CONTAINS("p"，[时间列]@row)， IF(VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) <> 12,12)， IF(VALUE(LEFT([时间列]@row, FIND(":"，[时间列]@row) -1)) = 12， -12)))+(价值(中期(时间列@row,找到(“:”,[时间列]@row) + 1, 2)) / 60)

时间计算公式

评论

类别