Calculating Time in HH:MM:SS

Shawn Harris

I found this quite helpful, what I don't get is why are you dividing by 3600? Also, What would be a way to convert this excel formula to a Smartsheets formula, which will take the frames back to the timecode:

=TEXT(MOD(INT(INT(INT(ABS(D7)/FrameRate)/60)/60),60),"00")&":"&TEXT(MOD(INT(INT(ABS(D7)/FrameRate)/60),60),"00")&":"&TEXT(MOD(INT(ABS(D7)/FrameRate),60),"00")&";"&TEXT(MOD(ABS(D7),FrameRate),"00")

SteCoxy

3600 seconds = 60mins, so it's all to do with the converting to the according time value. Regarding your other query, I have a sheet that has a lot of formulas in to calculate time, which also incorporates frames and timecodes, so I could publish it to see if it might be of any help to you? It might have the formula in that you need. You'd be welcome to use it then as a template if useful?

Shawn Harris

That would be great!

Thanks,

SteCoxy

https://app.smartsheet.com/b/publish?EQBCT=7cc62f4ab84c4ec7ae196808d6ce3f34 <-- have a look at this and see what you think. There's probably some columns in there that you won't need, so feel free to use this as a template and amend as necessary :-)

Give me a shout if you've got any questions about it.

SteCoxy

@Paul Newcome - I've had some feedback from the team that are using the solution that the times appear to be slightly out.

I've just done some testing and it appears to be out by 2 seconds on the first row in screenshot (should be 00:31:29), its works fine on the second row but then is out by 2 seconds again on the last row (should be 00:34:22).

Any ideas on how to correct this? Your expertise is much appreciated.

SteCoxy

Just in case anyone's wishing to use the above formulae and solution for their own, I've had Smartsheet Support investigate this and they've advised the following:

After various steps of troubleshooting I discovered that the seconds were only miscalculated when they were being recorded in the Fr_In column.  

I was able to resolve this by adjusting the formula for the Fr_In column to use the same formula as the Fr_Out column.  

The formula is as follows: 

=IF([Time Code IN (in HH:MM:SS)]@row <> "", ((((VALUE(LEFT([Time Code IN (in HH:MM:SS)]@row, 2)) * 90000)))) + (VALUE(RIGHT([Time Code IN (in HH:MM:SS)]@row, 2)) * 25) + INT(VALUE(RIGHT(LEFT([Time Code IN (in HH:MM:SS)]@row, 5), 2)) * 1500))

If you use the above formula you should see all of the duration values that are currently miscalculated correct themselves. 

I also made a general enquiry about when a dedicated duration/time calculation function would be rolled out, as I've been informed since at least 2018 it was being actively developed. They advisd me the following:

Additionally, I am unable to confirm when or if the feature of time calculation will be built, but we have let our Product Team know about your request. Here is our Release Notes page where you can also subscribe to receive the latest Smartsheet product release notes on new capabilities and enhancements by email: https://www.smartsheet.com/release-notes

It appears either that it's not a priority, is in limbo or development hell, perhaps?

SkiPatrolScott

@SteCoxy am trying to calculate HH:MM, I do not need seconds, I have been trying to see where in your formula I can get this? I will be sending Patrollers to a scene [Start Time], then recording [Arrival], [Departure], [Conclusion]. I doubt I will need all the break outs but for sure at least one of those against the [Start Time] for a [Duration]. Can you screen shot a sheet that shows the Duration Formula?

Our entries will be based on time of day, most likely 12 hr clock, if Smartsheet had a Input Mask we might go with military time but not sure