时间公式-下拉选择
你好,
我搜索过了，时间公式似乎是一场持续不断的增强请求之战。寻求一些帮助，我有两个专栏;'Start Time:'和'End Time:'。在web表单和工作表上有一个下拉框，从上午12:00开始，到晚上11:55结束，所有增量为5分钟。例如12:00 AM, 12:05 AM等等…
我用了一个巨大的公式:
(开始时间:)=如果(1 < > ",INT(((((左(结束时间:1,发现(“:”,结束时间:1)- 1)=“12”,如果(或(找到(“a”,结束时间:1)> 0,找到(“p”,结束时间:1)> 0),0,12),价值(左(结束时间:1,发现(“:”,结束时间:1)- 1)))+如果(找到(“p”,结束时间:1)> 0,12))* 60 +价值(中期(结束时间:1,发现(“:”,结束时间:1)+ 1,2)))((如果(左(开始时间:1,发现(“:”,开始时间:1)- 1)=“12”,如果(或(找到(“a”,开始时间:1)> 0,找到(“p”,开始时间:1)> 0),0,12),值(左(开始时间:1,找到(”:“开始时间:1)- 1)))+(找到(“p”,开始时间:1)> 0,12))* 60 +价值(中期(开始时间:1,发现(“:”,开始时间:1)+ 1,2))))/ 60 ) + ":" + 如果((((((左(结束时间:1,发现(“:”,结束时间:1)- 1)=“12”,如果(或(找到(“a”,结束时间:1)> 0,找到(“p”,结束时间:1)> 0),0,12),价值(左(结束时间:1,发现(“:”,结束时间:1)- 1)))+如果(找到(“p”,结束时间:1)> 0,12))* 60 +价值(中期(结束时间:1,发现(“:”,结束时间:1)+ 1,2)))((如果(左(开始时间:1,发现“:”,开始时间:1)- 1)=“12”,如果(或(找到(“a”,开始时间:1)> 0,找到(“p”,开始时间:1)> 0),0,12),价值(左(开始时间:1,发现(“:”,开始时间:1)- 1)))+(找到(“p”,开始时间:1)> 0,12))* 60 +价值(中期(开始时间:1,发现(“:”,开始时间:1)+ 1,2))))- INT((((如果(左(结束时间:1,发现(“:”,结束时间:1)- 1)=“12”,如果(或(找到(“a”,结束时间:1)> 0,找到(“p”,结束时间:1)> 0),0,12),价值(左(结束时间:1,发现(“:”,结束时间:1)- 1)))+如果(发现(“p”,结束时间:1)> 0,12))* 60 +价值(中期(结束时间:1,发现(“:”,结束时间:1)+ 1,2)))((如果(左(开始时间:1,发现(“:”,开始时间:1)- 1)=“12”,如果(或(找到(“a”,开始时间:1)> 0,找到(“p”,开始时间:1)> 0),0,12),价值(左(开始时间:1,发现(“:”,开始时间:1)- 1)))+如果(找到(“p”,开始时间:1)> 0,12))* 60 +价值(中期(开始时间:1,发现(“:”,开始时间:1)+ 1,2))))/ 60 * 60)< 10,“0”)+(((((左(结束时间:1,发现(“:”,结束时间:1)- 1)=“12”,如果(或(找到(“a”,结束时间:1)> 0,找到(“p”,结束时间:1)> 0),0,12),价值(左(结束时间:1,发现(“:”,结束时间:1)- 1)))+(找到(“p”,结束时间:1)> 0,12))* 60 +价值(中期(结束时间:1,发现(“:”,结束时间:1)+ 1,2)))((如果(左(开始时间:1,发现(“:”,开始时间:1)- 1)=“12”,如果(或(找到(“a”,开始时间:1)> 0,找到(“p”,开始时间:1)> 0),0,12),价值(左(开始时间:1,发现(“:”,开始时间:1)- 1)))+如果找到(“p”,开始时间:1)> 0,12)) * 60 +价值(中期(开始时间:1,发现(“:”,开始时间:1)+ 1,2))))——INT(((((左(结束时间:1,发现(“:”,结束时间:1)- 1)=“12”,如果(或(找到(“a”,结束时间:1)> 0,找到(“p”,结束时间:1)> 0),0,12),价值(左(结束时间:1,发现(“:”,结束时间:1)- 1)))+如果(找到(“p”,结束时间:1)> 0,12))* 60 +价值(中期(结束时间:1,发现(“:”,结束时间:1)+ 1,2)))((如果(左(开始时间:1,发现(“:”,开始时间:1)- 1)=“12”,如果(或(找到(“a”,开始时间:1)> 0,FIND(“p”,开始时间:1)> 0),0,12),价值(左(开始时间:1,发现(“:”,开始时间:1)- 1)))+(找到(“p”,开始时间:1)> 0,12))* 60 +价值(中期(开始时间:1,发现(“:”,开始时间:1)+ 1,2))))/ 60 * 60)," ")
这个工作很好，直到我把它改为上午/下午，因为我的员工正在与军事时间作斗争。有什么建议或改进，我可能错过了搜索?
其次，是否有一种简单的方法将增量转换为分钟的小数?例如:7:05 PM - 10:00 PM转换为2.92?
问候,
Rockee
评论
-
L_123 ✭✭✭✭✭✭转换成分钟比输出小时容易得多。
类似的
如果(右(Col1 2) =“点”,12 * 60 0)+价值(左(Col1, 2)) * 60 +价值(左(右(Col1, 5), 2)
是一个数字的分钟数。(假设是两位数的小时数。我不想花时间使用len来正确解析它，您已经展示了您知道如何这样做。)
用同样的公式得到另一个数的分钟数，然后相减。然后转换回时间。
-
L_123 ✭✭✭✭✭✭
我意识到我只需要解析时间，所以我就这么做了。
=IF(RIGHT([Start Time] 1,2) = "PM"， 60 * 12,0) + (VALUE(LEFT([Start Time]1, VALUE(LEN([Start Time]1) - 6))) * 60) + VALUE(LEFT(RIGHT([Start Time] 1,2)， 2)))
-
Rockee ✭
谢谢你的回复，卢克。我在“计算”列中尝试了你的公式，它给出了一个错误:#UNPARSEABLE -我将继续摆弄它，看看我是否能让它运行。你可能会认为，在其他平台(Google Sheets和Excel)上如此简单的事情，在Smartsheet上就不需要动脑筋了。
-
L_123 ✭✭✭✭✭✭
你投入的时间是什么形式的?
10:15 PM是我用的公式，我刚刚意识到你的公式后面有一个“:”。这可能就是问题所在
-
L_123 ✭✭✭✭✭✭
试试吧
=如果(右(结束时间:1,2)=“点”,60 * 12,0)+(值(左(结束时间:1,值(LEN(结束时间:1)- 6)))* 60)+价值(左(右(结束时间:1,5),2))——(如果(右(开始时间:1,2)=“点”,60 * 12,0)+(值(左(开始时间:1,值(LEN(开始时间:1)- 6)))* 60)+价值(左(右(开始时间:1,5),2)))
-
Rockee ✭
这个公式在计算分钟(早上7:25 -晚上9:00)时似乎不太明智。
=ROUND(MinDiff1 / 60) + "H" + (MinDiff1 / 60 - ROUND(MinDiff1 / 60)) * 60 + "M"
最后我只取了持续时间;=IF(RIGHT([结束时间:]1,2)= "PM"， 60 * 12,0) + (VALUE(LEFT([结束时间:]1)- 6))* 60)+ VALUE(LEFT(RIGHT([结束时间:]1)，5)，2))- (IF(RIGHT([开始时间:]1,2)= "PM"， 60 * 12,0) + (VALUE(LEFT([开始时间:]1)- 6))+ VALUE(LEFT([开始时间:]1)- 6))+ VALUE(LEFT([开始时间:]1)，5)，2))除以60，这也给了我以小数表示的分钟。我的最后一个问题是弄清楚如果他们在午夜结束工作，因为没有像那样的公式，并把它变成什么负时间。
谢谢你的帮助。
-
L_123 ✭✭✭✭✭✭
我忘了在四舍五入中减去。5来保证四舍五入。我想这应该能解决问题。
我回到一张旧纸上，对它进行了回顾和编辑。它比这个更深一点，因为它也增加了天数，这可能是你午夜问题的一个选择。(老实说，只需重命名您的列，复制粘贴公式，然后重新命名您的列)这样您就可以跟踪日期和时间。有一个小故障，我还没弄清楚在哪里，在开始和停止之间不到一分钟，它变成了-23H，但我不认为这是一个大问题。如果发生这种情况，我只是使用条件格式使其为空。在智能手机上跟踪任何少于1分钟的事情是没有任何意义的。
单击复选框，然后保存以刷新行。我有一份个人复印本，所以别担心弄乱了。
https://app.smartsheet.com/b/publish?EQBCT=dcf71ecb7f184467ab8f5debb68a04da
-
Rockee ✭
可怕的路加福音!
这给了我一些灵感!我做了一些错误检查，然后加入了日期和时间，更改了列名以匹配您的列名，我得到了一个错误- #无效的数据类型。看看我可能错过了什么(附屏幕截图)。
-
L_123 ✭✭✭✭✭✭
你能在回复中发布这个等式吗?另外，检查列属性并确保它是文本而不是日期。
-
Rockee ✭
有一个列(Edited)被设置为Date，尽管这不会改变“Minutes”列的输出。
使用你的公式:
= (NETDAYS (Input1 Edited1) - 1) *(24 * 60) +如果(右(Edited1 2) =“点”,12 * 60 0)+值(如果(LEN (Edited1) = 17日右(左(Edited1 11), 2),右(左(Edited1, 10), 1))) * 60 +价值(左(右(Edited1, 5), 2))——(如果(右(Input1 2) =“点”,12 * 60 0)+值(如果(LEN (Input1) = 17日右(左(Input1 11), 2),右(左(Input1, 10), 1))) * 60 +价值(左(右(Input1, 5), 2)))
链接:
http://publish.smartsheet.com/d95c669fb5c442cda043febc544ae757
-
L_123 ✭✭✭✭✭✭
啊，我脑子出了问题，以为你在计算结束时间。你需要用汽车专栏来计算工作日。如果你看看我的，一个是创建的，另一个是修改的。否则，系统不会识别出该信息是日期。
-
Rockee ✭
嗯，有没有办法让它识别日期/时间?您的公式正是我想要实现的，因为我可以将持续时间转换为十进制格式的小时和分钟，而且我更喜欢日期/时间格式。需要注意的是，所有这些都来自一个web表单，数据可能会在一天后输入，这取决于他们工作的时间。
-
L_123 ✭✭✭✭✭✭
我将删除日期修改列，并将其拆分为日期和时间列。你需要稍微修改一下公式。使用日期列，并让他们分别在web表单中输入日期和时间。
-
Rockee ✭
谢谢，现在就是这样，我只是隐藏了日期和时间列，以显示使用JOIN函数的一列。
