Calculating Time, minutes and seconds

I’m creating a scoring sheet for a timed competition. The time is in minutes and seconds that it takes to complete the task. They can get time penalties while doing this and it add to their time making the Time + Penalties = Adjusted Time

I created 2 additional helper rows to calculate the time and penalties based on posts I read to calculate time. When I use this formula and divide it by 60 it adds time.

VALUE(LEFT(Time@row, FIND(":", Time@row) - 1)) + VALUE(RIGHT(Time@row, 2)) /60

What am I doing wrong?

If I can get these 2 rows to calculate correctly, I was going to use the next formula to calculate the Adjusted Time.

=[Time Calc]@row + [Penalties Calc]@row

Results.png

Thank you for your help.

Find more posts tagged with

Accepted answers

All comments

Yasar_6

Hi, The issue is your formula order, you’re only dividing the seconds by 60, not the full time properly.

VALUE(LEFT(Time@row, FIND(":", Time@row) - 1)) + (VALUE(RIGHT(Time@row, 2)) / 60)

If helped support with a "yes"

Paul Newcome

Your Calc columns look to be in order already including the [Adjusted Time Calc] column. Is it that you are just having trouble converting that decimal back into a time format?

=INT([Adjusted Time Calc]@row) + ":" + RIGHT("0" + ROUND(([Adjusted Time Calc]@row - INT([Adjusted Time Calc]@row)) * 60, 2), 2)

Patty Clark

On the first line my real time is 16:21, the Time Calc is showing 16.35, not 16.21. It's the same with the Penalties 1:10 is now showing 1.16667 which is adding time. If this is done in Excel, Time + Penalties = Adjusted Time 16:21 + 1:10 = 17:31. My Adjusted Time in Smartsheet is showing 17:51667. I'm not sure why it's adding time.

Patty Clark

Ok, when I used the formula you provided it does calculate back out correctly. Thank you! I was thinking the calculated field would show the same.

I appreciate your help!

Paul Newcome

Happy to help. 👍️

Smartsheet is still working on time calcs. Your first step of converting the times into numerical values then adding the numbers together was correct. "16.35" is 16.35 minutes. 16 minutes plus .35 minutes.

So 1:10 showing as 1.16667 is not adding time. It is showing as 1 minute and a fraction of a minute (1/6th). It is just the difference between the display and the amount. They are actually the same thing.

Patty Clark

Thank you for the explanation. I understand now.

Patty Clark

Hello,

Another scoring sheet needed is timed in minutes, seconds, and milliseconds. I took my scoring sheet for minutes and seconds and used it for a template so the formula in Time Calc & Penalties Calc does not include the milliseconds. The testing formula column has the formula below in it.

I’ve tried modifying the formula so it will find the “.” and divide by 1000. This formula gives me an #INVALID VALUE

My thought was I would start value left again, find “.” add value right 2 places and divide by 1000.

What am I missing?

=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1)) + VALUE(RIGHT(Time@row, 2)) / 60 + VALUE(LEFT(Time@row, FIND(".", Time@row) - 1) + VALUE(RIGHT(Time@row, 2)) / 1000)

MM.SS.mmm.png

I appreciate your time and help with this.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.