In the mad technological rat race for high-definition, video compression and desktop compositing with infinite layers and undo levels, it’s not very often that you stumble upon a 20-kilobyte file from 1996 that can change the way you work. In the process of editing documentary films, I spent a while looking for the right way to enter timecode values in an Excel spreadsheet. This can be extremely valuable when working with historical archive footage. Most of the time the editor will be working with files which have “hard” timecode (numbers burned-in on top of the image to prevent illegal broadcast). When the film is done, it is usually up to the editor to dress up a list of all the shots he or she has actually used, so that the final footage – a “clean” frame, free of timecode – can be ordered from the appropriate footage agency.
If you’re the organized type, dressing such a list in Excel can be a pain. For each timecode value entered, you have to type three separate colons so as to obtain, e.g. 10:00:00:00 (asking for a clip that starts at 10000000 will usually get you nowhere). Moreover, if you want to do a few basic calculations (e.g. finding the total duration of all footage coming from a particular agency), you have to go through them with a separate timecode calculator and then waste half a day copying and pasting. I’ve tried to create a formula which would handle all of that but I got lost, quickly, in Excel’s cryptic syntax. And I didn’t feel like learning how to program Excel.
Luckily, as often happens on the third paragraph, an easy and effortless solution exists. A Swiss company, Belle Nuit Montage, has posted on its website an invaluable little macro which goes by the positively baroque name of TC.XLA 1.1. (with Drop-Frame). This little marvel was written more than a decade ago by Matthias Bürcher and will take all timecode formatting and calculating tasks in Excel off your hands. There are versions for Macintosh Excel 4 & 5 – and even DOS! – however the version called “Macintosh Excel 5, downloadable for OSX” is working perfectly well in a more up-to-date environment (in my case, Excel 2004 running on OSX Leopard).
Unfortunately, the English version of the webpage comes with very few instructions (the French version is much more explicit). The idea is very simple: you have to launch the XLA file (either by opening it or by adding it to your default macros). This adds two new styles (which can be found using Format > Styles…), called Time Code and Time Code DF (for Drop-Frame). All you have to do once you’ve applied these styles to your cells is to type the timecode without the colons – much like you would do in Final Cut Pro or Avid – i.e. typing 10000000 will result in 10:00:00:00 being displayed. This isn’t half the story, though.
TC.XLA running in Excel 2004
TC.XLA adds a dozen of new and handy functions that are very simple to use. You can now easily add and subtract timecodes, convert between frame rates and even find the value of a timecode in feet for 16mm or 35mm film. The macro seems to support all standard drop and non-drop frame rates, although I’ve only worked with it in 25fps (which is the rate the code defaults to if you don’t specify another one).
The syntax for the functions is very simple and is described on the Belle Nuit Montage website. As an example, entering “TCminus(E4;D4)” into a cell will give you the timecode difference between the values in E4 and D4. Presuming the two cells represent IN and OUT points, the result is obviously the duration of the shot. You can then easily add up a list of those durations with TCsum. This last function doesn’t seem to refresh properly when changing values upstream, but forcing a recalculation (Command-=) solves the problem. This is a minor niggle, though, compared to the ease of use and practical value of this tiny piece of free software.
Update: The above works fine for Excel 2008 as well, however you must press Command-Option-T after creating your workbook in order to enable the macro. The status line should respond with “TC-XLA 1.1″ followed by a URL to confirm this.
17 Comments
Hey, man. I downloaded the plug-in. And now I can’t get it to work. I’m on a mac running excel 2004 and Leopard OSX. I’m not extremely familiar with excel, but I followed your instructions, allowed macros, launched, but I can’t find it under:
format -> styles
I dunno, any ideas? How can I enter the macro manually?
Thanks
After loading the XLA file in Excel and creating a new workbook, try pressing Command-Option-T. The status line at the bottom of of the workbook should say something like TC XLA 1.1 followed by a URL. The styles should then be available in Format > Style. I’ve just discovered this myself when trying it on Excel 2008 (the original post refers to Excel 2004). I’ll update it accordingly. Let me know how it goes.
I tried it the Excel 2008 OX and no good.. Whow you who think we would find a working marco somewhere in the world…
“Macro”
Unfortunately I haven’t the slightest idea on how to program Excel macros. The only thing I could suggest is to contact the author directly and see if he feels like doing a code revision…
Thank you so, so much!!! Got it working and it will save me such a headache.
I am having a problem understanding setting up the formula to figure out the duration if I provide and in TC and an out TC.
Look at the screenshot I took for the post. The formula in the formula bar uses “TCminus” and a semicolon. The syntax is a bit exotic but it works like a charm.
Tal, thanks for the reply. I entered the formula exactly as you have it and keep getting “The formula you typed contains an error”. I am unclear on what the “TCminus” is in the formula.
Thanks so much
Excellent info! Unfortunately, I’m stuck with the same problem as Mr. Scott Davis is having. For the duration column, I did put in “=TCminus(C3;B3)” but I get an error. It says “Microsoft Excel found an error in the formula you entered. Do you want to accept the correction proposed below?
=TCminus(C3:B3)
I accepted the correction, but I get a “#Value!” in the cell.
When I put in “=TCminus(C3+B3)”, I get another error message:
“Macro Error. Names cannot look like references. Macro error at cell [TC.XLA]TC source corr1!E2″
I must be missing a step somewhere. Sorry for my ignorance, but can you help me with a step by step instructions on how to get a “duration” between two time codes?
I am currently running an Excel 2008 on a MacBook 2.4 GHz Intel Core Duo, 4GB RAM, Mac OS X, version 10.5.8.
Thanks so much!
Hi there – I would LOVE help adding and subtracting time code in excel. I was so happy to find this program, but it is not working
Like Walt, I get the same message when I try to use the TCminus formula. However, when I try to change the syntax to use a semicolon instead of a regular colon, it wont recognize the second data point, and the equation does not work. How do i get it to recognize the semicolon in the formula?
Thanks!
Tal, thanks for the wonderful writeup. I’ve been using TC.xla for a little over a year now, but having clear documentation in English has been very helpful.
Great! Finally I’ve found the solution I’ve been looking for so long! And, I got it to work as well!
So, many thanks to you for explaining it so well, and – of course – to Matthias for developing the plugin in the first place! I’m already a devoted user of Matthias’ Belle Nuit software, but had missed the fact that he also had developed this little helper.
Regards
Thomas Blomberg, London
How do you change the default frame rate?
Thanks so much for any help
Marigna
What a fantastic tool! That I can’t get to work. I’ve been getting all sorts of macro errors, or just plain wrong math [everything is either 21 frames, or 23;59;59;06].
If someone would be willing to post [or email me] a laundry list of how to get this thing working, or maybe an .xls that works, I’ll record and post an animated tutorial for all to see.
If the macro is otherwise functional on your system then I haven’t the slightest idea what could be the reason. I can only suggest that your recheck your formula for a possible typo or that you contact the author. There’s really nothing else I can do since I don’t know anything about programming Excel macros.
I, too, have installed the macro…and cannot get formulas to work.
Mac intel dual core 2.6, 10.4.11, 10gig RAM, Excel for Office 2004 11.4.1
I get #VALUE…have made sure that calculation columns are styled for TCdrop. I’m gonna quit and restart, see if that fixes is, then contact ol’ Belle Nuit…
And I just figured out why the macro returns “#value”.
In my case, I applied the TCdrop style onto two columns of pre-typed timecode numbers (in the usual format hh:mm:ss:ff). When I applied the style, the display of the number didn’t change…however, the actual characters entered INCLUDED the colons…so when the cell was processed by the TCplus macro, it saw not “hhmmssff”, but “hh:mm:ss:ff”…and couldn’t process the colons.
I guess I have to re-type all the numbers, but at least the offset calculations will be done for me as I go!