Calculate Running Time

jambla

Hi,

I want to calculate the running total of a movie. Each row is a different scenes start point. I want to add all together to get a running total.

 

In this example the first scene starts at 0:00 the next scene starts at 1:45 then 3:28. So the run time for this example movie would be 5 minutes and 21 seconds (5:21).

Is there a way to do this in Smartsheets?

 

Thanks for the help!

Paul Newcome

The easiest way to do this is to break it down into minutes and seconds in separate columns.

 

To get hours...

 

=VALUE(LEFT([est. time]@row, FIND(":", [est. time]@row) - 1))

.

To get the minutes...

 

=VALUE(MID([est. time]@row), FIND(":", [est. time]@row) + 1, 2))

.

Add up your minutes and divide by 60.

 

Your integer will be added to the minutes, and the remainder will be multiplied by 60 to give you your seconds.

.

Then you would use another column to bring the minutes and seconds together with a delimiter of ":".

Mike Wilday

@Paul's solution is the best at this time. Smartsheet doesn't have any native time formulas yet. There have been talks that some are in the works but that has yet to be seen. Hopefully, soon! 

SPalmieri

@Paul Newcome I'm trying to use your formula to do the same thing that Jambla asked about but I'm not sure I understand your formula. Could you please talk me through it? If it's easier, happy to share the sheet I'm created as a template. Thank you in advance.

Paul Newcome

@SPalmieri Which formula do you need more detail on?

SPalmieri

Our videos will all be under an hour, so I don't think we need the hour formula. But we have minutes and seconds and need to figure out how to add them all up correctly. I'm still new at formulas but am trying to follow what the minute formula you have is:

=VALUE(MID([est. time]@row), FIND(":", [est. time]@row) + 1, 2))

Add up your minutes and divide by 60.

I understand what VALUE and MID do think I get what the first part =VALUE(MID([est. time]@row) is doing. Can you walk me through what the 2nd half of the formula does?

And then how do I set this up in my sheet? We currently have one column dedicated to estimate time, similar to Jambla's example but called Seg Time Estimate. Do I keep that column as is but then make 2 other columns, 1 for minutes and 1 for seconds?

Apologies if all of this is basic. As I said, I'm still new :) I know SS is going to be the best option to set up this template but I'm struggling with how to get it to work :) Thank you in advance.

Paul Newcome

@SPalmieri There is actually a typo in that formula that I didn't catch before. The setup you're going to need is going to depend on your needs though. How is your time currently displayed?

hh:mm:ss

mm:ss

other?

And how do you want the end result displayed?

hh:mm:ss

mm:ss

other?

SPalmieri

@Paul Newcome Thanks for your help! Right now our time is displayed as mm:ss, which is also how we want the end result displayed. We're going to move the Seg Time Estimate to be the Column header and then would like to total to appear in that space.

Please let me know what other info would be helpful. Thank you again!

Screen Shot 2020-03-30 at 10.53.48 AM.png

Paul Newcome

Ok. And how do you want the end result displayed? mm:ss?

SPalmieri

Yes, please. Our allotted time is only an hour including commercials which won't be included in the SS so I don't think we need to worry about hours.

Paul Newcome

Awesome, and finally... How is your sheet going to be setup? Are you going to have the total in the parent row above the children rows that are getting summed, or...?

SPalmieri

That's the plan. Someone created the original template but didn't know very much about SS so I'm trying to reorganize it for them now that I'm involved. I can share the version I currently have, if that's helpful.

Paul Newcome

If you are using a parent row as a summary for the child rows, you can use something like this...

Insert a [Time Help] column of the text/number type.

In the child rows of the [Time Help] column, you would use a formula of

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

This will convert your time into a number.

For the parent row in your [Time Help] column, you would use

=SUM(CHILDREN())

And finally in the Parent row of your [Time Column]:

=INT([Time Help]@row) + ":" + ([Time Help]@row - INT([Time Help]@row)) * 60

SPalmieri

Thanks, @Paul Newcome ! I think I made it work in my SS! And I can hide the [Time Help] column to avoid confusing people, correct?

Do you mind walking me through what the formula is actually doing so that I can learn how to make them moving forward?

For the Time Help child rows {=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + (VALUE(RIGHT([Time Column]@row, 2)) / 60)} I understand what the value, left/right, find : is all about but don't understand what that - 1 is for. And then it's taking the rest of it and dividing it by 60 for seconds. Or am I totally wrong?

I think I get what the Time Column Parent formula {=INT([Time Help]@row) + ":" + ([Time Help]@row - INT([Time Help]@row)) * 60} is doing. It's taking the integer portion of the Time Help column, then adding ":", then taking the rest of that number and diving it by 60 to get seconds. Yes?

I'd love to understand what it's actually doing, if you don't mind explaining it :D

Paul Newcome

That is correct. You can hide the helper column if you want.

Explanation for the "-1":

We want to tell the LEFT function how many digits to pull. The FIND function will generate a numerical value based on where within the string the ":" is found. We want to stop at the character BEFORE the ":", so we subtract 1 from the value output by the FIND and that tells the LEFT function how many characters to pull.

So in 1:00, the ":" is in position 2. We do not want to pull 2 characters though because that second character cannot be converted into a numerical value. 2 - 1 = 1, so the output from FIND(":", [Time Column]@row) - 1 would end up being 1 for our LEFT function so it pulls the first character from the text string that can then be converted to a numerical value for calculations using the VALUE function.

The "divide by 60" portion is actually focused on the seconds which is pulled by the RIGHT function and converted to a number by the VALUE function. So basically it will provide a decimal that represents how many seconds are in the minute.

0:30 = 30 seconds

30/60 = .5

So 1:30 would use the VALUE(LEFT(............., FIND(............) - 1)) to pull the 1, and the VALUE(RIGHT(...........))/60 converts 30 seconds into half of a minute and when combined generate 1.5.

The Parent row formula in the [Time Column] is essentially working that backwards. We use the INT function to pull the integer which is your number of minutes.

So INT(1.5) = 1

Then we throw in our ":". Then we take the decimal which is the total minus the integer and multiply by 60.

.5 * 60 = 30.

So the output looks something along the lines of

INTEGER + ":" + (Remainder multiplied by 60)

Does that clear things up for you? Time calculations in Smartsheet can get rather complex rather quickly, so feel free to let me know if you need more detail.

SPalmieri

That explanation was perfect! I wasn't thinking about the -1 being related to the characters but thought it was actually trying to subtract 1 from the number, which was confusing me greatly! This is brilliant. I really appreciate you taking the time to walk me through it :) Thank you!!!