Calculating Time Duration

wmedina_intuitev

Hello everyone - I would appreciate some assistance with calculating time. I have been through the 19 page discussion on time and have tried multiple formula combinations, but cannot seem to resolve the specific issue I am having.

Basically, I need to be able to automatically calculate the hours and minutes between various start and end times. I have converted everything to military time, and am using decimals - which works wonderfully for most of the calculations - as long as the minutes (after the decimal) is under 60. So in the screen shot below on the second row, I need the TIME column to show 54 minutes (or 0.54) rather than 0.94. I know WHY the result is coming out like that, I just don't know how to convert it. I've tried so many things and my brain is fried. Any help appreciated!

Ray Lindstrom

Hi @wmedina_intuitev,

I don't believe you can get that kind of result with the columns as they are. Here's a great post by @Paul Newcome which should help guide you through calculating times as Smartsheet is not setup natively to do so.

BRgds,
-Ray

wmedina_intuitev

@Ray Lindstrom - yes, this is the post I referenced that I have searched through over hours. I tried multiple options from within the post and for most of the records it works great - the exceptions are for when the tens and hundreds values (after the decimal) exceed 0.59 for the calculation, then it just turns into a math calculation instead of a time calculation…….

wmedina_intuitev

I think I found a work around if anyone is interested.

1st helper row - I calculated start and end time simply (SUM(Stop@row-Start@row)

2nd helper row - if the decimal was greater than .59, enter a "1"

3rd helper row - if the 2nd helper row has a "1", add 1, and subtract .60 from the 1st helper row

The unhidden column checks if the second helper row has a 1 - if not, it populates the first helper row, if yes, it populates the 3rd helper row.

It's not pretty, but it seems to work!