Cue sheets are a fairly bread & butter part of an online editor's life. The last thing I do when I've completed an online job is to printout a copy of the show's cue sheet and place it in the box with the tape. Broadcasters and clients expect it to be there, and it can become an essential tool for other post-production people further down the line, such as other editors who may have to be reversioning your show, and would like to know where the textless elements are.
You can easily just copy the timecodes and durations that Avid (or FCP, or Premiere etc) gives you - but I like the automatic check that you can get when your spreadsheet works out durations from the timecodes you've entered.
Up until recently I've been using the excellent Excel Timecode Add-in which adds some timecode specific functions to Excel. However, I've recently moved to a facility that's using OpenOffice (hey, we spent all the money on plugins, OK) so I needed a solution that would work in this suite.
The solution was to write a couple of custom functions in OpenOffice Basic that would work with timecode formatted as text (i.e. "09:56:13:10") and allow me to add and subtract these values. So, if you would like to have your own cue sheets that add themselves up, please follow these instructions:
1. Download & install OpenOffice for your operating system.
2. Start the Calc program in OpenOffice
3. Choose Tool > Macros > Organize Macros > OpenOffice.org Basic...
On the new window that appears choose the Edit button
4. Copy and paste the following text (2 Custom functions - the text can go before or after the Sub Main...End Sub text that's already there)
Function FRAMESTOTC(a,b)
Dim HrsStr, MinsStr, SecsStr, FramesStr as String
Dim HrsFinalStr, MinsFinalStr, SecsFinalStr, FramesFinalStr as String
Dim MinsInt, SecsInt, FramesInt as Integer
Dim HrsInt as Long
FramesInt = a mod b
FramesStr = FramesInt
If Len(FramesStr) = 1 Then
FramesFinalStr = "0" & FramesStr
Else
FramesFinalStr = FramesStr
End If
SecsInt = ((a-FramesInt)/b) mod 60
SecsStr = SecsInt
If Len(SecsStr) = 1 Then
SecsFinalStr = "0" & SecsStr
Else
SecsFinalStr = SecsStr
End If
MinsInt = ((a-SecsInt*b-FramesInt)/(60*b)) mod 60
MinsStr = MinsInt
If Len(MinsStr) = 1 Then
MinsFinalStr = "0" & MinsStr
Else
MinsFinalStr = MinsStr
End If
HrsInt = ((a-MinsInt*b*60-SecsInt*b-FramesInt))/(60*60*b) mod 60
HrsStr = HrsInt
If Len(HrsStr) = 1 Then
HrsFinalStr = "0" & HrsStr
Else
HrsFinalStr = HrsStr
End If
FRAMESTOTC = HrsFinalStr & ":" & MinsFinalStr & ":" & SecsFinalStr & ":" & FramesFinalStr
End Function
Function TCTOFRAMES(a,b)
Dim HrsString as String
Dim HrsInt as Integer
Dim HrsConvToFrames as Long
HrsString = left(a,2)
HrsInt = HrsString
HrsConvToFrames = HrsInt*60*60*b
Dim MinsString as String
Dim MinsInt as Integer
Dim MinsConvToFrames as Long
MinsString = Mid(a,4,2)
MinsInt = MinsString
MinsConvToFrames = MinsInt*60*b
Dim SecsString as String
Dim SecsInt as Integer
Dim SecsConvToFrames as Integer
SecsString = Mid(a,7,2)
SecsInt = SecsString
SecsConvToFrames = SecsInt*b
Dim FramesString as String
Dim FramesInt as Integer
FramesString = right(a,2)
FramesInt = FramesString
TCTOFRAMES = HrsConvToFrames+MinsConvToFrames+SecsConvToFrames+FramesInt
End Function
5. Close the My Macros & Dialogs... window (the one that you've just pasted into)
6. Download this example cue sheet that uses the custom functions that you've just installed.
Some notes and caveats.
- The functions have the same arguments TCTOFRAMES(a;b) and FRAMESTOTC(a;b) where a is the timecode and b is the framerate.
- The timecode MUST be in the form HH:MM:SS:FF as text. The functions search the exactly 11 character timecode to find the hours, minutes, seconds and frames.
- This only works with simple frame rates (24, 25, 30, 50 etc).
- The functions need to be cut and pasted onto each machine where you want to use the cue sheet.
Good luck - and feel free to amend or improve the functions as you see fit. It's probably fairly obvious I'm not a developer - so go for it.
