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!

Tags:

• ✭✭✭✭✭✭

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 "

• ✭✭✭✭✭✭

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!

• ✭✭✭✭
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?

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!