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 5-10 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
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!