Hours and Minutes calculation

Emily T.

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

Nic Larsen

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 "

Nic Larsen

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.

Thank you so much! That worked!

Emily T.

@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

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.

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

Nic Larsen

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

Emily T.

I appreciate your helping me with this! Thank you!

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

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.

@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

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.