Wsrtjtyk

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!!

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:

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.

I change my answer.

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