按天、小时和分钟计算两个日期之间的时间
评论
-
保罗,
不知道你是否还在关注这条线索…但是。
你的配方好像对我不起作用。我有以15分钟为增量的时间下拉列表。
我得到了一些有趣的结果。
开始 ------------ 停止 ---------- 小时
中午12:30至1:30 -11:00
中午12:30 - 5:00 - 7:30 -30
上午10点到下午3点到7点我添加了下划线的数据看起来像数据。
任何想法吗?
提前感谢，
马特
-
她名叫Stara ✭✭✭✭✭✭嗨,马特,
如果表格或副本包含敏感信息，你可以分享吗?我很乐意帮你看看。
祝你一周愉快!
最好的
她名叫Stara
工作流顾问@完成咨询
Smartsheet专家顾问和合作伙伴
W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35
关于Smartsheet、集成、一般工作流建议或其他方面的帮助，请随时与我联系。
-
保罗新来的 ✭✭✭✭✭✭马特,
我的公式是没有上午和下午的24小时时钟。它被用来寻找1300是下午1点。一点应该是凌晨一点。
我还没有完全测试过12小时的长时间。它太长了，如果您不小心使用列名，它将超过每个单元格4000个字符的限制。
说实话，我已经忘记了这篇文章，一旦我找到了不同的解决方法，我就再也没有更新过它。我会翻翻我的笔记和旧帖子，看看能不能给你找点东西。我很确定Andree和我最近在另一篇文章中讨论过这个问题(可能在过去的一两个月里)。
thinkspi.com
-
看到了吗，这就是天一变就会发生的事
-
看到
-
ker9 ✭✭✭✭✭✭我喜欢你的解决方案!
不幸的是，我遇到了一个问题。我正在使用您的“AutoColumn Min Between”和“AutoColumn Time Down”公式，直到(系统)创建时间为12:44 PM和(系统)修改时间为1:45 PM(两者都在同一天)。
中间的分钟数是-659
当我使用手动部分时，它正确地显示为61分钟。
(处理时间的好方法，干得好!)
-
保罗新来的 ✭✭✭✭✭✭
在这里已发布的工作表，其中使用了几个辅助列。首先，我们将创建的时间转换为一个数值，以便(例如)9:30 AM = 9.5, 9:30 PM = 21.5。然后我们对Modified时间做同样的操作，但是我们也为Modified日期大于Created日期的每一天添加24(小时)。
然后我们从修改的数字中减去创建的数字，以数值形式给出持续时间，可以在未来的计算中使用，如SUMIFS等。
最后，我加入了一个[Display Duration]列，它将持续时间转换回hh:mm格式以供显示。这是一个文本字符串，不能用于数值计算(这就是CalcDuration列的用途)。
thinkspi.com
-
ker9 ✭✭✭✭✭✭
谢谢你！我会试试你的方法。
-
remkin ✭
嘿,
想听点疯狂的事吗。
我把第二个时间戳去掉了第一个时间戳。我发现误差是2.48，所以我把它应用到公式中，它起作用了。
老实说，我不相信这是我第一次战胜智能手机的局限。相信我，我都遇到过。
我希望这能帮助到一些人。
-
嗨@Paul新来的，
我有个问题要问你。我使用您的公式和辅助栏。问题是每次我保存表格时，修改时间列都在变化，就好像表格在重新计算持续时间，而自动修改时间列认为这是一行中的变化。你能帮我一下吗(见日期和Heure确认命令)?
帮助文章资源欧宝体育app官方888
类别
I hope you're well and safe!<\/p>
Try something like this.<\/p>
=IF([ShipC Updated]@row = 1, 1)<\/p>
Did that work\/help? <\/p>
I hope that helps!<\/p>
Have a fantastic week & Happy Holidays!<\/p>
Best,<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"},{"commentID":355142,"body":" @ReneeW<\/a> <\/p> Excellent!<\/p> You're more than welcome!<\/p> You were off with the space at the beginning and the parenthesis at the end.<\/p> =IF_<\/strong>([ShipC Updated]@row = 1)<\/strong>, 1<\/p> ✅Remember! <\/strong>Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up\/Awesome or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-21 19:45:29","updateUser":{"userID":156200,"name":"ReneeW","url":"https:\/\/community.smartsheet.com\/profile\/ReneeW","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-21T20:54:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":98993,"type":"question","name":"COUNTIFS Formula","excerpt":"Good morning, I am trying to do a COUNTIFS formula and getting a #Unparseable error here is the formula I am trying to set up. =COUNTIFS([Date]:[Date],(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 12),([Exception Category]:[Exception Category], \"TILA\/RESPA\")) Thanks for your help.","categoryID":322,"dateInserted":"2022-12-21T18:01:00+00:00","dateUpdated":null,"dateLastComment":"2022-12-21T19:19:33+00:00","insertUserID":130906,"insertUser":{"userID":130906,"name":"Ivan Perez","url":"https:\/\/community.smartsheet.com\/profile\/Ivan%20Perez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-21T19:19:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":130906,"lastUser":{"userID":130906,"name":"Ivan Perez","url":"https:\/\/community.smartsheet.com\/profile\/Ivan%20Perez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-21T19:19:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3343297233,"url":"https:\/\/community.smartsheet.com\/discussion\/98993\/countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98993\/countifs-formula","format":"Rich","lastPost":{"discussionID":98993,"commentID":355135,"name":"Re: COUNTIFS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/355135#Comment_355135","dateInserted":"2022-12-21T19:19:33+00:00","insertUserID":130906,"insertUser":{"userID":130906,"name":"Ivan Perez","url":"https:\/\/community.smartsheet.com\/profile\/Ivan%20Perez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-21T19:19:56+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-21T19:19:52+00:00","dateAnswered":"2022-12-21T19:01:27+00:00","acceptedAnswers":[{"commentID":355129,"body":"
