How to calculate total time on Smartsheet

therapy_mindset

Hello,

How can I calculate the total minutes on the smartsheet if I was able to obtain the start time and end time?

I’m having some issues with the total minutes. As you can see in the last submission... if I do 1305-1245 it indicates 60 minutes. However, the real answer is 20 minutes. 

Please see the photo below:

Many thanks in advance!

Samuel Mueller

You'll have to work through this formula. Also - the minute portion of my formula for minutes is divided by 60 - so 2+1/2 hours comes out 2.5, 2 hours 15 minutes comes out 2.25. First block below gives you the hours, plus the second block below gives you minutes with respect to an hour.

You can remove the /60 in the second one and maybe add some text in between formulas to put hours and minutes. or if you only want minutes then multiple the first block by 60 to get minutes instead of hours, and then remove the /60 from the second block and add both blocks together.

But to get it to work you have to separate hours and minutes from the military time.

=VALUE(IF(LEN([Time End (Military)]@row) = 3, LEFT([Time End (Military)]@row, 1), IF(LEN([Time End (Military)]@row) = 4, LEFT([Time End (Military)]@row, 2)))) - VALUE(IF(LEN([Time Start (Military)]@row) = 3, LEFT([Time Start (Military)]@row, 1), IF(LEN([Time Start (Military)]@row) = 4, LEFT([Time Start (Military)]@row, 2)))) +

IF(VALUE(RIGHT([Time End (Military)]@row, 2)) - VALUE(RIGHT([Time Start (Military)]@row, 2)) < 0, VALUE(RIGHT([Time End (Military)]@row, 2)) - VALUE(RIGHT([Time Start (Military)]@row, 2)) + 60, VALUE(RIGHT([Time End (Military)]@row, 2)) - VALUE(RIGHT([Time Start (Military)]@row, 2))) / 60,

Samuel Mueller

You'll have to work through this formula. Also - the minute portion of my formula for minutes is divided by 60 - so 2+1/2 hours comes out 2.5, 2 hours 15 minutes comes out 2.25. First block below gives you the hours, plus the second block below gives you minutes with respect to an hour.

You can remove the /60 in the second one and maybe add some text in between formulas to put hours and minutes. or if you only want minutes then multiple the first block by 60 to get minutes instead of hours, and then remove the /60 from the second block and add both blocks together.

But to get it to work you have to separate hours and minutes from the military time.

=VALUE(IF(LEN([Time End (Military)]@row) = 3, LEFT([Time End (Military)]@row, 1), IF(LEN([Time End (Military)]@row) = 4, LEFT([Time End (Military)]@row, 2)))) - VALUE(IF(LEN([Time Start (Military)]@row) = 3, LEFT([Time Start (Military)]@row, 1), IF(LEN([Time Start (Military)]@row) = 4, LEFT([Time Start (Military)]@row, 2)))) +

IF(VALUE(RIGHT([Time End (Military)]@row, 2)) - VALUE(RIGHT([Time Start (Military)]@row, 2)) < 0, VALUE(RIGHT([Time End (Military)]@row, 2)) - VALUE(RIGHT([Time Start (Military)]@row, 2)) + 60, VALUE(RIGHT([Time End (Military)]@row, 2)) - VALUE(RIGHT([Time Start (Military)]@row, 2))) / 60,

therapy_mindset

Hello Samuel,

You're awesome! Thank you for helping out!