Calculating Time in HH:MM:SS

SteCoxy
SteCoxy ✭✭✭✭✭✭

Hello, I'm hoping someone might be able to help me with calculating time in Smartsheet. We work with production teams in broadcasting, as well as other non-production teams, so calculating time is becoming rather vital, as more and more departments are using Smartsheet for a varying number of use cases: people and resource management, pre/post-production admin etc.


An old colleague of mine actually managed to create a sheet that was able to calculate it down to frames per second (HH:MM:SS:fps), as he was a technical whizz. The workflow is you type in the start and end times of of when an item has been shown (first 2 columns), then in column 4 (Fr_In) it uses a formula:

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

to converts the first column's value into numerics. The example on the dark blue row is, in this instance, what 10 hours equates to 900,000 frames.


An equivalent formula is then used to work out the frames per minute numerical value for the output in column 5 (Fr_Out):

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


Then the 2 figures are deducted in column 6 (Fr_Dur) to work out the difference in time duration in frames per second. My colleague then used some helper columns (the last 3 columns) to convert the value in column 6 into hours, minutes and seconds, in each associated column, using the following formulas respectively:

=IF(([Fr_Dur]14 / 90000) < 10, "0" + (INT([Fr_Dur]14 / 90000)), (INT([Fr_Dur]14 / 90000))) + ":"

=IF(INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 90000) * 90000)) / 1500) < 10, "0" + INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 90000) * 90000)) / 1500), INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 90000) * 90000)) / 1500)) + ":"

=IF(INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 1500) * 1500)) / 25) < 10, "0" + INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 1500) * 1500)) / 25), INT(([Fr_Dur]14 - (INT([Fr_Dur]14 / 1500) * 1500)) / 25))

I'm in the process of trying to tweak his old sheet, as for this particular use case, the team doesn't need to collate the data as granular as frames but we still need it down to the second (HH:MM:SS).


At first glance, the above might look like it's working the values out correctly but the last 2 rows, in fact, have slightly wrong duration values, by 3 and 2 seconds respectively. I appreciate this does sound pedantic, however, timings in broadcasting are essential!!


I've looked up a number of solutions in the Community Pages and then been wracking my brains trying to apply these suggestions to my problem here but I can't seem to get them to work. I'm hoping someone might be able to assist in simplifying the solution than it is currently.


I've published a version of this if it makes it any easier trying to play with the formulas: https://app.smartsheet.com/b/publish?EQBCT=b181d63d35f2473fb14c1757a0508b10. Any guidance and or pointers are much appreciated.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    My apologies for the delay again. Things have been moving pretty quickly for me lately, but I am back with (hopefully) a solution.

    So basically we have

    Start = hh:mm:ss

    End = hh:mm:ss


    and you want to know the difference between the two. There are two options for the output. Going with your current layout of "hh:", "mm:", and "ss:" in their own columns, we would use something like this...

    In the [Start Helper] column:

    =(VALUE(LEFT([Start Time]@row, 2)) * 3600) + (VALUE(MID([Start Time]@row, 4, 2)) * 60) + VALUE(RIGHT([Start Time]@row, 2))


    In The [End Helper] column:

    =(VALUE(LEFT([End Time]@row, 2)) * 3600) + (VALUE(MID([End Time]@row, 4, 2)) * 60) + VALUE(RIGHT([End Time]@row, 2))


    Then in the Duration column:

    [End Helper]@row - [Start Helper]@row


    Finally we take that total and break it out into the separate "hh:', "mm:", and "ss:" columns like so...

    hh: =

    =INT(Duration@row / 3600) + ":"


    mm: =

    =INT((Duration@row - INT(Duration@row / 3600)) / 60) + ":"


    ss: =

    =Duration@row - (INT(Duration@row / 3600) * 3600) - (INT((Duration@row - (INT(Duration@row / 3600) * 3600)) / 60) * 60) + ":"

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!