Hours and Minutes calculation

Hi,

I have a Start Time column and an End Time column and I would like it to calculate the hours and minutes of duration into another column. I scoured Smartsheets and found information on this, see link below, but when I try the formula, which should give me 0 hour(s) and 45 min, I get 0 hour(s) and 4500 min. Can someone please tell me where I went wrong? Thank you!



https://help.smartsheet.com/function/time?_gl=1*faaset*_ga*MjI1NDg5NDE3LjE2ODkwMTI4NTg.*_ga_ZYH7XNXMZK*MTcxMDg2OTMwMy43NS4xLjE3MTA4NzA4OTAuNC4wLjA.&_ga=2.174769376.336178535.1710786964-225489417.1689012858&_gac=1.49021268.1710176702.CjwKCAjw17qvBhBrEiwA1rU9w3-7NhiTvsM-UCbSmZ9KsuY2dDrmJQhcB1pTzGuqZM3BRfYyZ46-xhoCnjAQAvD_BwE

Tags:

Best Answer

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    I also copied their formula and couldn't get it to result correctly. I had to revise it slightly. This seems to work.

    =ROUNDDOWN((TIME([End Time]@row) - TIME([Start Time]@row)) * 24) + " hour(s) and " + ROUNDDOWN(VALUE(RIGHT((TIME([End Time]@row) - TIME([Start Time]@row)) * 24, FIND(".", (TIME([End Time]10) - TIME([Start Time]@row)) * 24))) * 60) / 100 + " minutes "

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    I also copied their formula and couldn't get it to result correctly. I had to revise it slightly. This seems to work.

    =ROUNDDOWN((TIME([End Time]@row) - TIME([Start Time]@row)) * 24) + " hour(s) and " + ROUNDDOWN(VALUE(RIGHT((TIME([End Time]@row) - TIME([Start Time]@row)) * 24, FIND(".", (TIME([End Time]10) - TIME([Start Time]@row)) * 24))) * 60) / 100 + " minutes "

  • Emily T.
    Emily T. ✭✭✭✭

    Thank you so much! That worked!

  • Emily T.
    Emily T. ✭✭✭✭
    edited 03/19/24

    @Nic Larsen - Will this work only for 15 minute increments? I noticed that the formula doesn't yield the correct results for 5-10 minute slots. For 5 minutes it calculates to 19.8 minutes and for 10 minutes it's 40.2. Thoughts?

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    I think I had a parenthesis one spot off. Try it like this:

    =ROUNDDOWN((TIME([End Time]@row) - TIME([Start Time]@row)) * 24) + " hour(s) and " + ROUNDDOWN(VALUE(RIGHT((TIME([End Time]@row) - TIME([Start Time]@row)) * 24, FIND(".", (TIME([End Time]11) - TIME([Start Time]@row)) * 24))) * 60 / 100) + " minutes "

  • Emily T.
    Emily T. ✭✭✭✭

    Hmmm.... 10 minutes reads as 40 minutes and 5 minutes reads as 19 minutes?

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Well that's not right. Hmm... let me try again.

  • Emily T.
    Emily T. ✭✭✭✭

    I appreciate your helping me with this! Thank you!

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    This one is really frustrating, but I suspect the issue on my end is we have our day set to 8 hours and in their TIME documentation, I need to edit it to 24 to make this formula work. But I am not sure what the ripple effect might be in that case across our Org's sheets.

    "Convert a time difference into a string of hours and minutes (only works when working day = 24 hr"

    I'll see if I can figure out a solution without making global changes. But I have a feeling that's the issue.

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Okay..... I give credit to this video here... what a life saver: https://www.youtube.com/watch?v=J2jrhCfsDy0

    Note: In Project Settings in my test sheet, I had to enable Dependencies and edit length of day to 24hours.

    Then:

    1) My start time and end time columns are just text and I've entered the time as 1:30 PM or 5:00 AM or 5:32 PM, etc....

    2) I created 2 helper columns for the time and labeled as End Time - 12 HR and Start Time - 12 HR.

    3) Placed these formulas in the Helper Columns:

    =TIME([End Time]@row, 0, 2) and =TIME([Start Time]@row, 0, 2)

    4) Created an additional helper column called Time Difference with the following formula:

    =TIME([End Time - 12 HR]@row) - TIME([Start Time - 12 HR]@row)

    5) In my Column to display Hours and Minutes, I used this formula

    =ROUNDDOWN((TIME([Start Time - 12 HR]@row) - TIME([End Time - 12 HR]@row)) * 24) + " Hour(s) and " + ([Time Difference]@row * 24 - INT([Time Difference]@row * 24)) * 60 + " Minutes"


    Smartsheet Help page could use a lot of work and credit should go to the guy who made the Youtube video. So helpful.

  • Emily T.
    Emily T. ✭✭✭✭

    @Nic Larsen - Sorry for the delay! This was super helpful and it worked! I really appreciate your help here! Thank you so much! I do have one question. For some reason I have two Duration times that lists 2 hours as 1 hour 60 minutes. All the rest of those that are similar read correctly. I did watch the video and tried to see if I could even replicate what he did with the formula without luck. I made sure the formulas were the same for all cells in my sheet. Wondering if you have any thoughts on these two outliers?



  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Glad it worked, but hmm... That's really interesting. I don't know off hand... I might have to play with the formula some more to see if I can figure out why it's doing that in random cases.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!