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