Calculating HH:MM

SkiPatrolScott

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

SkiPatrolScott

@L_123 @Paul Newcome

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

L_123

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.

SkiPatrolScott

@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

Paul Newcome

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:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

L_123

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.

SkiPatrolScott

@L_123 @Paul Newcome

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

L_123

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)))

SkiPatrolScott

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...

L_123

I agree. Smartsheet is missing 2 main functionalities right now for me, dynamic dropdowns and time formulas.