Calculate the time between two dates by days, hours and minutes

2»

Comments

  • Paul,

    Not sure if you're still following this thread...but.

    You're  formula doesn't seem to be working for me. I have dropdowns for time in 15 minute increments.

    I'm getting some interesting results.

    Start------------Stop----------Hours

    12:30 PM___1:30 PM___-11:00

    12:30 PM___5:00 PM___-7:0-30

    10:00 AM___3:00 PM___-7:00

    I added the underscores to the data looks like data-ish.

     

    Any thoughts?

     

    Thanks in Advance,

    Matt

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 11/14/18

    Hi Matt,

    Can you share the sheet or a copy if it contains sensitive information? I'd be happy to take a look.

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Matt,

     

    My formula is for a 24 hour clock without AM or PM. It is made to look for 1300 being 1:00 PM. 1:00 would be 1:00 AM.

     

    The long one for the 12 hour clock I haven't tested fully. It is so long that if you are not careful with your column names, it will exceed the 4,000 character per cell limit.

     

    I honestly had forgotten about this post and never updated it once I figured out a different workaround. I'll dig through my notes and old posts and see if I can find something for you. I am pretty sure that Andree and I had discussed this in another post more recently (maybe in the past month or two).

  • see this is what happens when day changes

    Capture.JPG

  • ker9
    ker9 ✭✭✭✭✭✭

    @L@123

    I love your solution!

    Unfortunately, I'm running into a problem. I'm using your formulas from "AutoColumn Min Between" and "Auto Column Time Down", which worked until the (system) Create time was 12:44 PM and (system) Modified time is 1:45 PM (both on the same day).

    The minutes between came out as -659

    When I use the manual portion, it comes out correctly at 61 minutes.

    (Awesome method to handle time, great job!)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @ker9 It sounds like it converted the 12 to 00 (midnight instead of noon). I have a few time solutions floating around here in the Community. I will see if I can dig up one of the solutions that uses the System columns. I know I have it somewhere. I just gotta find it.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @ker9

    HERE is a published sheet that uses a couple of helper columns. First we convert the Created time into a numerical value so that (for example) 9:30 AM = 9.5 and 9:30 PM = 21.5. Then we do the same for the Modified time, but we also add 24 (hours) for every day that the Modified date is greater than the Created date.


    Then we subtract the Created Number from the Modified Number to give you the duration in a numerical value that can be used in future calculations such as SUMIFS and whatnot.


    Finally I included a [Display Duration] column that converts the duration back into a hh:mm format for display. This is a text string and cannot be used for numerical calculations (that's what the CalcDuration column is for).

  • ker9
    ker9 ✭✭✭✭✭✭

    @Paul Newcome

    Thank you! I will give your method a try.

  • remkin
    remkin ✭✭

    Hey,

    Want to hear something crazy.

    I made it work I took the second timestamp and removed the first timestamp. I found the error was a ratio of 2.48 so I applied that to the formula and it worked.

    I honestly dont beleive it its the first win Ive had battling smarthseet limitations. And trust me Ive come across them all.

    I hope this helps someone.


  • Hi @Paul Newcome ,

    I have a question for you. I use your formula and helper columns. Problem is everytime I save my sheet, modification time column is changing, as if the sheet is recalculating duration and the automatic column of modification time think it's a change in the row. Can you help me (see column Date & Heure confirmation commande ou soumission)?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!