White Paper - Timecode Spreadsheet March 29, 2004
Using the Timecode Log Spreadsheet
White Paper - Timecode Spreadsheet March 29, 2004
Using the Timecode Log Spreadsheet
Download the Timecode Log Spreadsheet
HERE
Further Note: You must have Microsoft Excel to open this file.
A Little Background
A recent thread on LAFCPUG.org
raised the question of whether one could use a spreadsheet, like
Excel, to log timecode and have it automatically calculate duration
for each clip, as well as overall duration. I volunteered to
look into it, and here is the result. Now you can log shots and
determine duration without having to run back and forth to a
timecode calculator. Accompanying this document is an Excel spreadsheet
with the formulas to accomplish this. Consider it a template,
to be expanded and modified to your needs. Feel free to contact
me with questions and kudos.
Some Details
I am going to assume that you
know your way around Excel or similar spreadsheets and that you
have some knowledge of formulas and functions in spreadsheets.
The code in here is pretty simple, and it is yours to do with
as you wish. This document will tell you how to navigate and
what to expect, but won't try to explain how every detail works.
I offer this as a useful tool to the FCP community, nothing more.
This document accompanies an example Excel spreadsheet named Timecode Log.xls. There are a number of cells with formulas in them, and if you accidentally type something in a formula cell you will replace the formula with what you type, and that cell will no longer do the job it was supposed to. Not to worry! It is easily fixed. I suggest that you make a backup copy of this example spreadsheet (and never touch it) to keep as a reference to the formulas it contains.
Feel free to modify the sample spreadsheet
as you wish to add fields or rearrange things, copy it at will,
or create your own from scratch using the formula coding found
in the file. It doesn't do much more than log clips with In and
Out timecode (with space for a description) and automatically
calculate the duration of the clip in timecode format, while
also keeping a running total of the duration of all the clips
you have logged so far.
Data entry will take some getting used to. I explored several
alternative ways of entering the data and they all have some
drawbacks (Excel doesn't know much about video). I settled on
this method because it makes the most use of Excel's built-in
time formatting and calculating abilities. That way, if you get
erroneous results I can gleefully point the finger at Excel ;-).
I made this spreadsheet using Excel X for Mac. If you're using something else, I don't know if you'll be able to open it, but the formulas and formats are pretty basic and should be adaptable to another spreadsheet program like MS Works or Appleworks.
Data Entry
The workflow is to operate in
one row per clip, moving left to right (nothing unusual about
that). Excel navigates from cell to cell, one column to the next,
by pressing the TAB key. In the Start column enter the
HH:MM:SS part of the In point timecode, being sure to put a colon
between the hours and minutes and between the minutes and seconds.
It must be entered this way, with zeroes holding the places.
For example, if you are entering 4 minutes and 12 seconds, you
must type 0:4:12. If your entry does not readjust into strict
HH:MM:SS format (2 digits for each field) see the section below
on Formatting to fix it. Then press the TAB key to move to the
next column to enter the frames (this column is not labeled,
but it is right after the Start column. Don't use any punctuation
in this field, only the number of frames in the timecode. You
don't need to type a leading zero, but it will be automatically
formatted into the field to make it look traditional.
Then TAB to the End column to enter the HH:M:SS portion of the Out point timecode as before, and TAB again to enter the frames. At this point, pressing Return/Enter should complete the calculation and take you to the beginning of the next row, for your next entry. In some spreadsheet programs, the program has to "learn" this before it will act consistently.
The Duration (and frames) columns will be automatically calculated as you make these entries. Don't be alarmed that the Duration goes negative while you're entering Ins and Outs; it will be correct when you have finished them.
Also notice that the Total Running Time updates as you go, with the same intermediate nonsense until you finish the row. The nonsense is because Excel recalculates every time you change the contents of a cell, and the ones you haven't gotten to yet contain zero as far as Excel is concerned. You can turn off automatic calculation if you want, and just have it recalculate on demand, but I'll leave that to you to decide on and implement.
A Bunch of Caveats
I have provided a rudimentary
way to work in NTSC or PAL, by giving you a place at the top
of the spreadsheet to enter the FPS (frames per second). All
the calculations use this value, and if you change it you will
see an immediate change in the calculated values. It's not very
smart. All I do is use the value when adding and subtracting
frames to roll over to the next second when the number of frames
exceeds 29 (for NTSC) or 24 (for PAL) or goes negative. The numbers
for FPS should be 30 for NTSC or 25 for PAL, don't bother with
29.97 or 23.98; even though they will work, don't be fooled into
thinking this calculator is going to actually split the hairs
that fine. It doesn't know from DF or NDF. If these considerations
are important to you, let me know, but be prepared to give me
all info and math to work it in because I've never made much
sense of it myself.
I was a little surprised to discover that when the In and Out points are the same, i.e., have the same timecode, that the duration is 1 frame. I had assumed that would be zero duration, but doing some experimenting in FCP confirms it, and the duration field in the Browser reflects this. So this little tool does the same. You'll notice in the example spreadsheet where I have propagated the formula (Duration columns) but haven't entered anything in the Start and End, that these completely empty entries report a Duration of 1 frame. Check the "Keeping the Total Running Time Honest" section below to see how you can prevent these unentered rows from incorrectly affecting the Total Running Time calculation.
I have put a little error trapping in, which you may or may not find useful. If any frame entry is not between 0 and 1 less than the FPS setting at the top, you will see "frame value!" in red in the Validation column for that row. If you set FPS to 25 in the example spreadsheet, you'll see it in a couple of places because the TC I used as examples was based on NTSC. I have made no effort to trap for other kinds of entry errors, like entering a Start timecode that is later than the End timecode. You will notice that the Duration is negative in this case, however.
Formatting
To keep the data entry and display
looking familiar and consistent (and in some cases to make the
calculations work) certain groups of cells have been formatted
in particular ways. You may inadvertently remove the formatting
from certain cells, or need to know how to format a spreadsheet
you create from scratch, so here are some details.
Columns C, E and G in the example ("Start", "End" and "Duration"), plus cell I2
Format > Cells > Time > select the format that gives you HH:MM:SS.Columns D, F and H in the example (unlabeled, but immediately after Start, End and Duration)
Format > Cells > Custom; type in 00 to cause these values to display always with 2 digits. This isn't necessary for math reasons, it only makes it look prettier.
Keeping the Total Running Time Honest
Cell I2 simply sums the HH:MM:SS
values in the Duration column. In the example spreadsheet, this
sum encompasses rows 6 through 25, which includes rows that haven't
had anything entered into them yet. Since the unused rows have
zero time in them, this is of no consequence. Cell J2 is the
sum of the values in the frames column of the Duration
section. Notice that the frames rows have the value of
1 frame in them even where there is no entry. This is because
of the compensation I described earlier, where FCP treats a clip
with identical Start and End TC as being 1 frame in duration.
This is meaningless when there is no entry, so in order to keep
these rogue frames from affecting the Total they are not included
in the sum. Look at the formula in J2 and you will see this is
true, it only includes rows 6 through 11. In order to keep the
Total Running Time correct, the formula in I2 must include at
least all the rows that have entries in them (more than that
is OK), and J2 must include only the rows that have entries.
These formulas are easily kept up to date by manual entry or
using simple tools Excel provides. I'm not going to go into detail
here, but check out your spreadsheet program's Help or write
to me. But keep in mind that in a long show, an error of a few
dozen frames isn't going to be very significant. It makes sense
to update the sum scope now and then, but not constantly.
Extending the Spreadsheet
The sample spreadsheet's formulas
only extend as far as row 25, and I'll bet your project is longer
than that. As it stands, if you enter Start and End values beyond
row 25, no calculations will occur on them. It's real easy to
extend the formulas further down the spreadsheet. In Excel, you
can click on the last cell that has a formula in it and drag
down to fill the rows below it with the formula. Explore your
spreadsheet program's Fill Down command to see how it likes to
do it. Do this for columns G and H, and I if you like the Validation
function.
I hope you like it, and you find it useful!
copyright © 2004 Scott Taylor
Scott Taylor spends his days doing a little programming and database management, graphic and web design, and network troubleshooting for a small manufacturing company. By night he plots his newest breakthrough in the cinema world.
© 2000 -2004 Apple
Computer, Inc. All rights reserved. Apple, the Apple logo, Final
Cut Pro, Macintosh and Power Mac
are either registered trademarks or trademarks of Apple. Other
company and product names may be trademarks of their respective
owners.
All screen captures, images, and textual references are the property and trademark of their creators/owners/publishers.
