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!
Best 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

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 "

Thank you so much! That worked!

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

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 "

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

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

I appreciate your helping me with this! Thank you!

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.

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.

@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?

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
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!