Calculating Time Duration

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!

Find more posts tagged with

Accepted answers

All comments

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!

Babs_at_Didlake

Thank you for your post. I have been trying to calculate time to give me total hours and minutes for production activity. This is very helpful. There's hope! 😊

I'm also pretty new to Smartsheet. I'm not sure I understand your reference to helper rows. Could you further explain?

Appreciate your support.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.