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

2»

评论

  • matt69051
    matt69051

    保罗,

    不知道你是否还在关注这条线索…但是。

    你的配方好像对我不起作用。我有以15分钟为增量的时间下拉列表。

    我得到了一些有趣的结果。

    开始 ------------ 停止 ---------- 小时

    中午12:30至1:30 -11:00

    中午12:30 - 5:00 - 7:30 -30

    上午10点到下午3点到7点

    我添加了下划线的数据看起来像数据。

    任何想法吗?

    提前感谢，

    马特

    2018 - 11 - 13 - _15 - 47 - 32. - png

    · 在Facebook上分享 在推特上分享
  • 她名叫Stara
    她名叫Stara ✭✭✭✭✭✭
    编辑11/14/18

    嗨,马特,

    如果表格或副本包含敏感信息，你可以分享吗?我很乐意帮你看看。

    祝你一周愉快!

    最好的

    她名叫Stara

    工作流顾问@完成咨询

    Smartsheet专家顾问和合作伙伴

    https://workbold.com/

    她名叫Stara工作流顾问/ CEO @工作大胆

    W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35

    关于Smartsheet、集成、一般工作流建议或其他方面的帮助，请随时与我联系。

    · 在Facebook上分享 在推特上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    马特,

    我的公式是没有上午和下午的24小时时钟。它被用来寻找1300是下午1点。一点应该是凌晨一点。

    我还没有完全测试过12小时的长时间。它太长了，如果您不小心使用列名，它将超过每个单元格4000个字符的限制。

    说实话，我已经忘记了这篇文章，一旦我找到了不同的解决方法，我就再也没有更新过它。我会翻翻我的笔记和旧帖子，看看能不能给你找点东西。我很确定Andree和我最近在另一篇文章中讨论过这个问题(可能在过去的一两个月里)。

    thinkspi.com

    · 在Facebook上分享 在推特上分享
  • roy_ricky09
    roy_ricky09

    看到了吗，这就是天一变就会发生的事

    Capture.JPG

    · 在Facebook上分享 在推特上分享
  • roy_ricky09
    roy_ricky09

    看到

    Capture.JPG

    · 在Facebook上分享 在推特上分享
  • ker9
    ker9 ✭✭✭✭✭✭

    @(电子邮件保护)

    我喜欢你的解决方案!

    不幸的是，我遇到了一个问题。我正在使用您的“AutoColumn Min Between”和“AutoColumn Time Down”公式，直到(系统)创建时间为12:44 PM和(系统)修改时间为1:45 PM(两者都在同一天)。

    中间的分钟数是-659

    当我使用手动部分时，它正确地显示为61分钟。

    (处理时间的好方法，干得好!)

    · 在Facebook上分享 在推特上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    @ker9听起来像是把12点转换成了00点(午夜而不是中午)。我在社区里有一些时间解决方案。我看看能不能找到一个用到System列的解。我知道我把它放在什么地方了。我一定要找到它。

    thinkspi.com

    · 在Facebook上分享 在推特上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    @ker9

    在这里已发布的工作表，其中使用了几个辅助列。首先，我们将创建的时间转换为一个数值，以便(例如)9:30 AM = 9.5, 9:30 PM = 21.5。然后我们对Modified时间做同样的操作，但是我们也为Modified日期大于Created日期的每一天添加24(小时)。


    然后我们从修改的数字中减去创建的数字，以数值形式给出持续时间，可以在未来的计算中使用，如SUMIFS等。


    最后，我加入了一个[Display Duration]列，它将持续时间转换回hh:mm格式以供显示。这是一个文本字符串，不能用于数值计算(这就是CalcDuration列的用途)。

    thinkspi.com

    · 在Facebook上分享 在推特上分享
  • ker9
    ker9 ✭✭✭✭✭✭

    @Paul新来的

    谢谢你！我会试试你的方法。

    · 在Facebook上分享 在推特上分享
  • remkin
    remkin ✭

    嘿,

    想听点疯狂的事吗。

    我把第二个时间戳去掉了第一个时间戳。我发现误差是2.48，所以我把它应用到公式中，它起作用了。

    老实说，我不相信这是我第一次战胜智能手机的局限。相信我，我都遇到过。

    我希望这能帮助到一些人。

    Smartsheet赢一次。png


    · 在Facebook上分享 在推特上分享
  • 朱莉Barbeau
    朱莉Barbeau ✭

    嗨@Paul新来的，

    我有个问题要问你。我使用您的公式和辅助栏。问题是每次我保存表格时，修改时间列都在变化，就好像表格在重新计算持续时间，而自动修改时间列认为这是一行中的变化。你能帮我一下吗(见日期和Heure确认命令)?

    image.png


    · 在Facebook上分享 在推特上分享

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

想直接在Smartsheet中练习使用公式吗?

请查看公式手册模板!
Hi @ReneeW<\/a> <\/p>

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":"

@Ivan Perez<\/a> You need the conditional equation in your iferror statement. It also always follows criteria range, then criteria for each set. <\/p>

=COUNTIFS([Date]:[Date], IFERROR(YEAR(@cell) = 2022, 0), [Date]:[Date], IFERROR(MONTH(@cell) = 12, 0), [Exception Category]:[Exception Category], \"TILA\/RESPA\")<\/p>

if you wanted year and month combined, you could do that with the and function.<\/p>

=COUNTIFS([Date]:[Date], and(IFERROR(YEAR(@cell) = 2022, 0), IFERROR(MONTH(@cell) = 12, 0)), [Exception Category]:[Exception Category], \"TILA\/RESPA\")<\/p>

Let me know if that works.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-21 19:19:52","updateUser":{"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":"✭✭✭✭✭"}}},"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":98994,"type":"question","name":"Tracking actual time in percentages, reporting in hours?","excerpt":"A component is adamant about tracking their actual time on projects based on percentage of an 8-hour workday, but senior managers want to see actuals based on hours. Is there a formula or other way for staff to enter a percentage and convert that to hours for reporting purposes?","categoryID":322,"dateInserted":"2022-12-21T18:03:47+00:00","dateUpdated":null,"dateLastComment":"2022-12-21T19:01:23+00:00","insertUserID":156195,"insertUser":{"userID":156195,"name":"LynneDavis","url":"https:\/\/community.smartsheet.com\/profile\/LynneDavis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-21T19:51:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":142964,"lastUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/LBB70FV9GP52\/nY1AJCGUNDHDC.jpg","dateLastActive":"2022-12-21T22:44:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":27,"score":null,"hot":3343295710,"url":"https:\/\/community.smartsheet.com\/discussion\/98994\/tracking-actual-time-in-percentages-reporting-in-hours","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98994\/tracking-actual-time-in-percentages-reporting-in-hours","format":"Rich","tagIDs":[213,228,242,254,259],"lastPost":{"discussionID":98994,"commentID":355128,"name":"Re: Tracking actual time in percentages, reporting in hours?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/355128#Comment_355128","dateInserted":"2022-12-21T19:01:23+00:00","insertUserID":142964,"insertUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/LBB70FV9GP52\/nY1AJCGUNDHDC.jpg","dateLastActive":"2022-12-21T22:44:43+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:18:59+00:00","dateAnswered":"2022-12-21T19:01:23+00:00","acceptedAnswers":[{"commentID":355128,"body":"

Hi @LynneDavis<\/a>,<\/p>

You could use an additional column to do the conversion and report the number of hours. When one enters the percentage value, the new column (with a column formula) could multiply the % by 8 (hours in a workday).<\/p>

Would look something like this:<\/p>

=[Percentage Column Name]@row * 8<\/p>

So if the percentage was 75, the result would be 6.<\/p>

Just replace the text \"Percentage Column Name\" to the name of your own percentage column.<\/p>

You could also hide the new column that displays the hours, but still include the column in you reports.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-21 19:18:59","updateUser":{"userID":156195,"name":"LynneDavis","url":"https:\/\/community.smartsheet.com\/profile\/LynneDavis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-21T19:51:40+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":[{"tagID":213,"urlcode":"Conditional Formatting","name":"Conditional Formatting"},{"tagID":228,"urlcode":"Project Management","name":"Project Management"},{"tagID":242,"urlcode":"Resource Views","name":"Resource Views"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":259,"urlcode":"IT & Operations","name":"IT & Operations"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">