Calculating HH:MM
I saw a discussion with @SteCoxy , @Paul Newcome , @Shawn Harris where formulas were built to calculate duration all the way to FPS. I only need the HH:MM and would prefer [H] and excel where the hours calculate total along with the Minutes. I saw a couple of formulas but none that just calculated the "IN" HH:MM against the "OUT" HH:MM for the "Total" HH:MM.
Is there a simple formula for this? It seems Smartsheet is all about days... my data will be in minutes with a few hours in some instances.
Please help
Best Answer
-
I looked at all 13 pages, came close, even copied some formulas, but no luck. I thought I could live with creating a calculation formula but then started getting 4.9999 MIN. The last two are just typed in, what I was hoping to achieve, the Out - In in HH:MM. I noticed a number of the formulas do not account for the ":", the ones I did find also had a date in the formula, I tried to just remove that row but got my favorite #UNPARSEABLE
Answers
-
It depends on your format. Please give some examples of inputs that you want to subtract.
Simple is relative, it isn't going to be as easy as excel, but if depending on the format it could be somewhat straightforward.
-
@L_123 right now I dont have a specific format? as far as I can tell it will be number/text entered as 10:21 or 14:16 as a start time, then a finish time as 10:24 or 14:19 so that the duration would be 00:03...
Can you format the fields? When I looked at the Date Functions there were no options for adding hh:mm
-
We don't yet have any time formatting options. We have to use formulas in helper columns to run the calculations.
You may need to dig as this thread is now 13 pages long, but there should be a solution tucked away in there that should give you what you need:
-
When I said format I meant how you typed it in, not necessarily a cell format. If you don't find a solution in Pauls time formula sheet (there are a lot of good references there) let us know and one of us can type something up for you.
-
I looked at all 13 pages, came close, even copied some formulas, but no luck. I thought I could live with creating a calculation formula but then started getting 4.9999 MIN. The last two are just typed in, what I was hoping to achieve, the Out - In in HH:MM. I noticed a number of the formulas do not account for the ":", the ones I did find also had a date in the formula, I tried to just remove that row but got my favorite #UNPARSEABLE
-
I feel like I've done this in a better way before, but here is a working example then.
=VALUE(LEFT(Out@row, FIND(":", Out@row) - 1)) - IF(VALUE(RIGHT(Out@row, 2)) > VALUE(RIGHT(In@row, 2)), 0, 1) - VALUE(LEFT(In@row, FIND(":", In@row) - 1)) + ":" + IF(LEN((IF(VALUE(RIGHT(Out@row, 2)) > VALUE(RIGHT(In@row, 2)), 0, 1) + VALUE(RIGHT(Out@row, 2)) - VALUE(RIGHT(In@row, 2)))) = 1, 0, "") + (IF(VALUE(RIGHT(Out@row, 2)) > VALUE(RIGHT(In@row, 2)), 0, 1) + VALUE(RIGHT(Out@row, 2)) - VALUE(RIGHT(In@row, 2)))
-
Thankyou, not sure why it has to be so complicated. Smartsheet needs to get some better duration functions for those of us who are tracking multiple events within minutes...
-
I agree. Smartsheet is missing 2 main functionalities right now for me, dynamic dropdowns and time formulas.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!