按天、小时和分钟计算两个日期之间的时间

LouiseM · 06/06/18

你好,

我试图实现以下公式从我的excel电子表格到我的smartsheet:

= INT (O2-Q2)与“天”小时(MOD (O2-Q2 1))和“小时”分钟(MOD (O2-Q2 1))和“分钟”

基本上，我需要计算两栏(收到日期)和(文件发布日期)之间的时间，以天、小时和分钟为单位。

Cell 'O2'为发布日期，'Q2'为接收日期。

例如:我们在2018年5月5日10:30有一个新的业务请求，我们在2018年10月5日12:45发送了文件。在我的新专栏中，公式会计算这两者之间的时间为5天2小时15分钟。

所以我有一栏写着生意来的时间和日期。

还有一栏写着文件发出的时间和日期。

我试着把公式粘贴进来，我发现你需要在方括号中使用列的名称“[XX]”，而不是单元格字母和数字“A3”……但我所尝试的其他所有东西都不断出现“#unparseable”。

我希望你们能理解，有人能帮忙吗?

Bryana_S · 06/06/18

在我的经验中，Smart Sheets不知道如何计算时间。我很想知道是否有人找到了让它工作的方法!

保罗新来的 · 06/06/18

你可以写一个考虑am, pm和“:”的公式，但它是极其复杂的，非常容易打破。我能找到的唯一方法是使用24小时时间和使用MID函数来拉出两个小时数字和两个分钟数字，在分钟部分中factor /60，计算你的时间，然后把它们放在一起。再不过…相当复杂且容易破碎(虽然不像另一个那么容易)。如果你感兴趣，我可以把它贴在这里，但我不能撒谎……屁股很疼…

克里斯·麦凯 · 06/06/18

这也是我们在不久的将来放弃Smartsheet的原因。再一次，我们有了一个更新，除了bug修复之外，还引入了一些琐碎的功能(菜单栏中的活动界面，表单菜单等)，而像处理时间值或基于位置的准确日期的能力等缺点已经存在了两年多。

内容……根据我们的要求来整理你的开发优先级。这个社区不能离你的其他客户反馈流告诉你的太远。没有人会因为产品对日期/时间的处理如此糟糕而感到高兴。ob欧宝娱乐app手机下载特别是当我们花了数万美元购买一个PPM工具时，它应该具有这种功能。

LouiseM · 06/07/18

好的，谢谢你，这真的很有帮助，尽管听起来你在公式方面的专业知识比我先进得多!

如果你可以张贴它，我可以给它一个尝试，并找出如何做它!

这似乎不像我想的那么容易…

保罗新来的 · 06/07/18

这是12小时钟的。我还得翻我的笔记才能找到24小时计划。

= IFERROR (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,找到(“p”,【开始时间】1)> 0),0,12),价值(左(【开始时间】1,发现(“:”,【开始时间】1)- 1)))+(找到(“p”,【开始时间】1)> 0,12))* 60 +价值(中期(【开始时间】1,找到(“:”,【开始时间】1)+ 1,2))))/ 60 * 60),0)

当然[开始时间]1和[结束时间]1将被替换为你的列和行引用。如果你把1换成@row在美国，情况会更灵活一些。你需要注意的另一件事是……它已经有3179个字符了，并且有90个列引用。

每个单元格只能包含4000个字符(包括空格)。根据您的列名，您可以很快地超出这个范围。

注:Smartsheet不支持MOD功能。

LouiseM · 06/07/18

哇。

谢谢你的帮助，保罗，非常感谢。我要试试这个公式，看看会发生什么……我想我只需要添加(然后隐藏)一些名称较短的新列，以适应4000字符的阈值，因为我没有意识到这一点!

我想我可能只是写了几个小时坐下来，解决这个问题，我将尝试12小时的一个，如果那不工作，那么我可能不需要24小时的一个，希望它一切顺利，工作完美的第一次!

她名叫Stara · 06/07/18

你好,

我用了24小时。

= IFERROR (INT(((((左([1]停止@row;找到(“:”;[Stop 1]@row) - 1) = "12";如果(或发现(“a”;[Stop 1]@row) > 0;找到(“p”;[Stop 1]@row) > 0);0;12);值(左([1]停止@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) / 60) + ":" + IF(((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) - INT((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) / 60) * 60) < 10; "0") + ((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) - INT((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) / 60) * 60);0)

我希望这能帮助到你!

最好的

Andrée Starå -工作流程顾问@ Get Done

保罗新来的 · 06/07/18

这看起来像是12小时的钟，有上午和下午?它似乎比24小时的时钟要复杂得多，还是我读错了(很有可能)?

以下是我用来确定基于4位24小时时钟(0000 - 2359)的任务持续时间:

=((价值(中期(总和(完成时间)@row， 1, 2)) - VALUE(MID([开始时间]@row， 1, 2))， ""， SUM(VALUE(MID([完成时间])@row， 3,2)) - VALUE(MID([开始时间])@row， 3, 2))) / 60))

保罗新来的 · 06/07/18

顺便说一下…我在上面发布的那个邪恶的长公式仅供《时代周刊》使用。它没有考虑日期。考虑到它的当前长度，您可能需要为日期创建几个隐藏的支持列，然后编写一个快速JOIN公式来合并它们。

她名叫Stara · 06/07/18

也许有可能让它变得更好，但重要的是它是有效的。

最好的

她名叫

L_123 · 06/11/18

https://community.smartsheet.com/discussion/time-formula-drop-down-selection

https://app.smartsheet.com/b/publish?EQBCT=dcf71ecb7f184467ab8f5debb68a04da

保罗新来的 · 06/11/18

看看你，聪明先生…哈哈

我不能在你的工作表上“另存为”，所以我建立了一个像你的，复制粘贴公式。但我最终得到了一个不可解析的错误。

L_123 · 06/11/18

也许是帽子的问题?

L_123 · 06/11/18

你在时间中放入一个空间

哈，我对列名不是很友好。是更担心分子式吗

保罗新来的 · 06/11/18

哈哈。别担心。公式是棘手的部分。修正了我的列名，效果很好。我一定会把它保存为一个模板。先生,感谢。

我希望原来的海报看到这个!哈哈哈

探索。问。的答案。

按天、小时和分钟计算两个日期之间的时间

评论