Adding Timecodes in Excel

Question

I have about 140 cells (they are in a vertical column) containing Timecodes in the Format hh:mm:ss:ff. There are 24 Frames per second. I would like to add them, so I have a total duration of all extracts. Can anyone tell me how I could do that in Micosoft Excel? Any help is greatly appreciated, since I am literally clueless...Thanks so much in advance!!

Answer 1

You can get the duration in frames by doing some math. First you have to pick out each portion of the timestamp (Hours, Minutes, Seconds, and Frames), then just math that up to get the number of frames since some prior point in time (00:00:00:00). From that you can derive the duration each frame is from it's predecessor and sum the results for the total:

Answer 2

This might not be the best way (or maybe it is?), but it works.

1. Separate hh:mm:ss from ff.
2. Sum hh:mm:ss and ff respectively.
3. Add frames to hh:mm:ss.

Answer 3

This code is from my excel complement "TCCalculator". The calculator is free but not the code. I can put here the link to my Google Drive if the moderator gives me his permission

You must to do three things.

1- Create a Button to start the calculation (ButtonTC).
2- Create some VBA functions (I will show it to you)
3- Select a range and click on ButtonTC

With this method we can select ranges of different cells. It will make the sum of all.

All you need are these functions:

Private Sub ButtonTC_Click

Private Sub ButtonTC_Click()
    Dim framesRef As Double
    ‘framesRef can be 23.98, 24, 25, 29.97…
    'Beware, the decimal point may vary depending on the system configuration
    'The cell that will store the final result must be free of data. We can also get the result with a msgbox
        Cells("1", "A") = f_CalculateRangeTC(framesRef)
End Sub

Public Function f_CalculateRangeTC

Public Function f_CalculateRangeTC(ByVal framesRef As Double) As String
Dim obj_Cell As Range
Dim sumaTotalFrames As Double
        sumaTotalFrames = 0
        For Each obj_Cell In Selection.Cells
            With obj_Cell
                sumaTotalFrames = sumaTotalFrames + f_CalculateTcInFrames(.Text, framesRef)
            End With
         Next
    f_CalculateRangeTC = f_ConvertFramesTo_HHMMSSFF(sumaTotalFrames, framesRef)
End Function

Public Function f_CalculateTcInFrames

Public Function f_CalculateTcInFrames(ByVal numToConvert As String, ByVal framesRef As Double) As Double
    Dim fra2f, seg2f, min2f, hor2f As Double
    fra2f = 0
    seg2f = 0
    min2f = 0
    hor2f = 0
    ‘This two sentences convert an unformated number to correct format  1:23:05  to  00012305
    ‘But this not work with this:   1:1:02 (1 minute, 1 second, 2 frames)  becomes 00001102  ¡ERROR!
    numToConvert = Replace(numToConvert, ":", "")
    numToConvert =  Right("00000000" & numToConvert, 8)

    fra2f = Mid(numToConvert, 7, 2)                                 'Frames to frames
    seg2f = Mid(numToConvert, 5, 2) * (framesRef)               ‘Seconds to frames
    min2f = Mid(numToConvert, 3, 2) * (60 * framesRef)      ‘Minutes to frames
    hor2f = Mid(numToConvert, 1, 2) * (3600 * framesRef)        ‘Hours to frames
    sumaFrames = hor2f + min2f + seg2f + fra2f
     f_CalculateTcInFrames = sumaFrames        'Salimos de la función y devolvemos el valor.
Exit Function