I have created ten functions to help with timecodes. Each function returns a single value. They convert between timecode, frame count, and time. In Calc, the latter is stored as a number representing a fraction of a day and you can format it to show hours, minutes, seconds or any combination of those. The functions take two parameters.
• The first parameter is the timecode, frame count, or time to convert. The first parameter can also be a range. The values in the range are summed and the result is converted to the specified format.
• The second parameter is the number of frames per second. FR2CT and TM2CT do not require the second parameter. If you omit this when it's required you will receive an Argument is not optional error.
The set also includes functions to count the number of valid timecodes, frame counts, or times in the range. The functions which perform summation always ignore invalid data. Improperly formatted timecodes, negative frame counts, and negative times are invalid.
Examples, all cells containing timecodes at 30 frames per second:
• Timecode sum of A2 through A99 =TC2TC(A2:A99;30)
• Frame count sum of A2 through A99 =TC2FR(A2:A99;30)
• A2 is start, B2 is end, calculate timecode difference: =FR2TC(TC2FR(B2;30)-TC2FR(A2;30);30)
• Count invalid timecodes in A2:A99 =COUNTA(A2:A99)-TC2CT(A2:A99;30)
To install the Calc timecode functions:
• Select the lines below from Rem Timecode functions to the last End Function → Edit → Copy
• Tools → Macros → Organize Macros → OpenOffice Basic → Organizer → Modules
• Standard → New → Timecode → OK → Close
• Macro from → Standard → Timecode → Edit
• Edit → Select All → Edit → Paste → File → Save → Window → Close window
The functions are then available for your use.
To remove the Calc timecode functions:
• Tools → Macros → Organize Macros → OpenOffice Basic → Organizer → Modules
• Standard → Timecode → Delete → Yes → Close → Close
Earlier timecode topics:
[Solved] Create timecodes from time
[Solved] Convert hours:minutes:seconds:frames to minutes
[Solved] How to set time to TIMECODE ?
[Solved] User-defined time format (Timecode)
[Solved] Help converting frames to 'HH:MM:SS.FF.00'
[Solved] Timecode calculator formula not working
[Solved] Timecode difference between IN & OUT - hh:mm:ss:ff
[Solved] Sum for Timecodes
Rem Timecode functions ' V1R1M0 2024-08-29
Rem TC2CT: Count timecode cells ' Invalid timecodes are ignored
Rem TC2FR: Sum timecode cells → Frames ' Invalid timecodes are ignored
Rem TC2TM: Sum timecode cells → Time ' Invalid timecodes are ignored
Rem TC2TC: Sum timecode cells → Timecode ' Invalid timecodes are ignored
Rem FR2CT: Count frame cells ' Negative frame counts are ignored
Rem FR2TC: Sum frames → Timecode ' Negative frame counts are ignored
Rem FR2TM: Sum frames → Time ' Negative frame counts are ignored
Rem TM2CT: Count time cells ' Negative times are ignored
Rem TM2FR: Sum times → Frames ' Negative times are ignored
Rem TM2TC: Sum times → Timecode ' Negative times are ignored
Rem Timecodes are normally HH:MM:SS:FF but if Frames per second is larger than 99 the last group will have
Rem more than two digits. More than 24 hours are allowed and the first group can have more than two digits
Const _Double = 5 ' VARTYPE for Double
Const _String = 8 ' VARTYPE for String
Const _FStr = "Frame type not numeric" ' Incorrect type
Const _FNeg = "Frame type not positive" ' Incorrect value
Const _SecPerDay = 86400 ' Seconds in one day
Function [.TCCheck](T As Variant,FPS As Double) As Boolean ' Check for a valid timecode
Dim TStr() As String ' (0):HH (1):MM (2):SS (3):FF
Dim L As Integer : L=2 ' Length of frame count
If FPS>99 Then L = INT(LOG(FPS-1)/LOG(10))+1 ' Digit count for large FPS
[.TCCheck] = False ' Error return value
If VARTYPE(T) <> _String Then Exit Function ' Continue if string
TStr = SPLIT(T,":",5) ' Maximum of five parts
If UBOUND(TStr) <> 3 Then Exit Function ' T must be HH:MM:SS:FF
If LEN(TStr(0)) < 1 Then Exit Function ' No hours
If LEN(TStr(1)) <> 2 Then Exit Function ' Minute length wrong
If LEN(TStr(2)) <> 2 Then Exit Function ' Second length wrong
If LEN(TStr(3)) <> L Then Exit Function ' Frame count wrong
If TStr(0) Like "*[!0-9]*" Then Exit Function ' Hours not numeric
If TStr(1) Like "*[!0-9]*" Then Exit Function ' Minutes not numeric
If TStr(2) Like "*[!0-9]*" Then Exit Function ' Seconds not numeric
If TStr(3) Like "*[!0-9]*" Then Exit Function ' Frames not numeric
If TStr(1) > "59" Then Exit Function ' Too many minutes
If TStr(2) > "59" Then Exit Function ' Too many seconds
If VAL(TStr(3)) >= FPS Then Exit Function ' Too many frames
[.TCCheck] = True ' Return frame count
End Function
Function [.FrameCount](T As String,FPS As Double) As Double ' Return frame count for single timecode
Dim TStr() As String ' (0):HH (1):MM (2):SS (3):FF as strings
Dim Mult(3) As Long : Mult = Array(24,60,60,FPS) ' 24 hours 60 minutes 60 seconds FPS Frames
Dim I As Integer, Frames As Double : Frames = 0 ' Loop counter, Frame coount
TStr = SPLIT(T,":",5) ' Maximum of five parts
For I = 0 To 3 ' Hours, minutes, seconds, frames
Frames = Frames * Mult(I) + VAL(TStr(I)) ' Count of Frames for timecode
Next I ' End of loop
[.FrameCount] = Frames ' Return frame count
End Function
Function [.Timecode](F As Double,FPS As Double) As String ' Return timecode for single frame count
Dim Mult(3) As Long : Mult = Array(24,60,60,FPS) ' 24 hours 60 minutes 60 seconds FPS Frames
Dim HMSF(3) As Long, ZZ As String : ZZ = "00" ' Format code and components
Dim I As Integer, FF As Double : FF = F ' Loop counter, frame count
For I = 3 To 0 Step -1 ' Loop through components
HMSF(I) = FF MOD Mult(I) : FF = INT(FF/Mult(I)) ' Get frames, seconds, minutes, hours
Next I ' End of loop
HMSF(0) = HMSF(0) + FF*24 ' Add hours in remaining days
If FPS>99 Then ZZ = STRING(INT(LOG(FPS-1)/LOG(10))+1,"0") ' Format code for large FPS
[.Timecode] = FORMAT(HMSF(0),"00") & ":" & FORMAT(HMSF(1),"00") & ":" & _
FORMAT(HMSF(2),"00") & ":" & FORMAT(HMSF(3),ZZ) ' Return HH:MM:SS:FF
End Function
Function [.GetSum](R As Variant) As Double ' Get sum of positive numbers in range
Dim I As Long, J As Long ' Loop counters
Dim RSum As Double, E As Variant ' Range sum, range element
Select Case False ' Check range type
Case VARTYPE(R) < 99 ' Single element?; No, process array
Case VARTYPE(R) = _Double : [.GetSum] = 0 : Exit function ' Numeric?; No, sum is zero
Case R > 0 : [.GetSum] = 0 : Exit function ' Positive?; No, sum is zero
Case Else : : [.GetSum] = R : Exit function ' Sum is element value
End Select ' End of range check
For I = 1 To UBOUND(R,1) ' Outer loop
For J = 1 to UBOUND(R,2) ' Inner loop
E = R(I,J) ' Get element from range
Select Case False ' Check cell value
Case VARTYPE(E) = _Double ' Numeric?; No, ignore it
Case E > 0 ' Positive?; No, ignore it
Case Else : RSum = RSum + E ' Add to total frames
End Select ' End of case statement
Next J ' End of inner loop
Next I ' End of outer loop
[.GetSum] = RSum ' Return sum
End Function
Function TC2CT(R As Variant,FPS As Variant) As Variant ' Count valid timecode cells in range
Rem All timecodes in the range are checked and the count
Rem of valid timecodes is returned
Dim RCount As Double : RCount = 0 ' Range count
Dim I As Long, J As Long ' Loop counters
If VARTYPE(FPS) <> _Double Then TC2CT = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then TC2CT = _FNeg : Exit Function ' Frame speed must be positive
Select Case False ' Test for single element range
Case VARTYPE(R) < 99 ' Single element; No, process range
Case [.TCCheck](R,FPS) : TC2CT = 0 : Exit Function ' Good timecount; No, return 0
Case Else : TC2CT = 1 : Exit function ' Otherwise return 1
End Select ' End range test
For I = 1 To UBOUND(R,1) ' Outer loop
For J = 1 to UBOUND(R,2) ' Inner loop
If [.TCCheck](R(I,J),FPS) Then RCount = RCount + 1 ' If valid timecode add to count
Next J ' End of inner loop
Next I ' End of outer loop
TC2CT = RCount ' Count of frames in range
End Function
Function TC2FR(R As Variant,FPS As Variant) As Variant ' Convert timecode range to total frames
Rem All timecodes in the range are converted to frame counts
Rem and those counts are summed; Invalid timecodes are ignored
Dim RSum As Double : RSum = 0 ' Range frame sum
Dim I As Long, J As Long, T As Variant ' Loop counters, range element
If VARTYPE(FPS) <> _Double Then TC2FR = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then TC2FR = _FNeg : Exit Function ' Frame speed must be positive
Select Case False ' Test for single element range
Case VARTYPE(R) < 99 ' Single element; No, process range
Case [.TCCheck](R,FPS) : TC2FR = 0 : Exit Function ' Good timecount; No, return 0
Case Else : TC2FR = [.FrameCount](R,FPS) : Exit function ' Otherwise return frame count
End Select ' End range test
For I = 1 To UBOUND(R,1) ' Outer loop
For J = 1 to UBOUND(R,2) ' Inner loop
T = R(I,J) ' Get element from range
If [.TCCheck](T,FPS) Then RSum = RSum + [.FrameCount](T,FPS) ' If valid timecode add to total frames
Next J ' End of inner loop
Next I ' End of outer loop
TC2FR = RSum ' Sum → frames
End Function
Function TC2TM(R As Variant,FPS As Variant) As Variant ' Convert timecode range to total time
Rem All timecodes in the range are converted to Calc times
Rem and those times are summed; Invalid timecodes are ignored
If VARTYPE(FPS) <> _Double Then TC2TM = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then TC2TM = _FNeg : Exit Function ' Frame speed must be positive
TC2TM = TC2FR(R,FPS)/FPS/_SecPerDay ' Sum → frames → time
End Function
Function TC2TC(R As Variant,FPS As Variant) As Variant ' Convert timecode range to timecode
Rem All valid timecodes in the range are converted to frames
Rem and those frames are summed, then converted to a timecode
If VARTYPE(FPS) <> _Double Then TC2TC = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then TC2TC = _FNeg : Exit Function ' Frame speed must be positive
TC2TC = [.Timecode](TC2FR(R,FPS),FPS) ' Sum → frames → timecode
End Function
Function FR2CT(R As Variant,Optional FPS As Variant) As Variant ' Count nonnegative frame cells in range
Rem The count of nonnegative frame cells is returned ' The FPS parameter is ignored
Dim RCount As Double : RCount = 0 ' Range count
Dim I As Long, J As Long, E As Variant ' Loop counters, array element
Select Case False ' Test for single element range
Case VARTYPE(R) < 99 ' Single element; No, process range
Case R>=0 : FR2CT = 0 : Exit Function ' Good frame count; No, return 0
Case Else : FR2CT = 1 : Exit function ' Otherwise return frame count
End Select ' End range test
For I = 1 To UBOUND(R,1) ' Outer loop
For J = 1 to UBOUND(R,2) ' Inner loop
E = R(I,J) ' Get element from range
Select Case False ' Check cell value
Case VARTYPE(E) = _Double ' Numeric?; No, ignore it
Case E >= 0 ' Nonnegative?; No, ignore it
Case Else : RCount = RCount + 1 ' Add to count
End Select ' End of case statement
Next J ' End of inner loop
Next I ' End of outer loop
FR2CT = RCount ' Nonnegative count
End Function
Function FR2TC(R As Variant,FPS As Variant) As Variant ' Convert frame count range to timecode
Rem All frame counts in the range are summed and converted
Rem to a timecode; Only positive cells are summed
If VARTYPE(FPS) <> _Double Then FR2TC = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then FR2TC = _FNeg : Exit Function ' Frame speed must be positive
FR2TC = [.Timecode]([.GetSum](R),FPS) ' Sum → timecode
End Function
Function FR2TM(R As Variant,FPS As Double) As Variant ' Convert frame count range to time
Rem All frame counts in the range are summed and converted
Rem to a time; Only positive cells are summed
If VARTYPE(FPS) <> _Double Then FR2TM = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then FR2TM = _FNeg : Exit Function ' Frame speed must be positive
FR2TM = [.GetSum](R)/FPS/_SecPerDay ' Sum → frames → time
End Function
Function TM2CT(R As Variant,Optional FPS As Variant) As Variant ' Count nonnegative times in range
Rem The count of nonnegative times is returned ' The FPS parameter is ignored
TM2CT = FR2CT(R) ' Nonnegative count
End Function
Function TM2TC(R As Variant,FPS As Variant) As Variant ' Convert time range to timecode
Rem All times in the range are summed and converted to a
Rem timecode; Only positive cells are summed
If VARTYPE(FPS) <> _Double Then TM2TC = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then TM2TC = _FNeg : Exit Function ' Frame speed must be positive
TM2TC = [.Timecode]([.GetSum](R)*FPS*_SecPerDay,FPS) ' Sum → frames → timecode
End Function
Function TM2FR(R As Variant,FPS As Double) As Variant ' Convert time range to frame count
Rem All times in the range are summed and converted to a
Rem frame count; Only positive cells are summed
If VARTYPE(FPS) <> _Double Then TM2FR = _Fstr : Exit Function ' Frame speed must be numeric
If FPS < 1 Then TM2FR = _FNeg : Exit Function ' Frame speed must be positive
TM2FR = [.GetSum](R)*FPS*_SecPerDay ' Sum → frames
End Function
Do not ask questions in the Code Snippets forum. Instead, use the Calc forum.