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.
Update II (May 21st, 2010): To all current and future commenters, please understand that I cannot offer support for this plugin because I haven’t programmed it. It is apparently a hit and miss affair, some people having lots of luck with it and others getting quite frustrated with the whole thing. I myself can’t understand why the macro behaves the way it does. I’ve emailed the author, suggesting to upgrade the plugin, but apparently it cannot be upgraded and “somebody would need to write it from scratch using VBA”. There is, honestly, nothing more I can do about it.
27 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.
FYI-
If you haven’t tried it go to Tools>Add-ins and check the Tc box.
I missed that part but eventually figured it out.
JM
Just wanted to come back and share a few things for anyone who may need the information. I’m using Excel 2003 on XP.
I had a problem where I had to sync a device with a time stamp to a camera running TC at 30fps. The format of the device is hh/mm/ss. The time code is hh/mm/ss/ff. The device time is needed to do an animation in Adobe After Effects that will sync with the camera TC. The problem was that AE needed frames (eg. Cam TC 00:00:01:00 = 30 frames) but when the time stamp data (00:00:01 = 1 second) was used the seconds were interpreted as frames. The above example would end up on the timeline as frame one rather than thirty. With me? I’m plotting thousands of frames and can’t manually type in frames into the time stamp in Excel. But Excel won’t allow (or I don’t know how) frames in its time formats. Here’s how I solved the problem:
I loaded the macro per instruction and hit ctrl t. I also made sure it was activated in Tools>Add Ins.
I made 3 columns.
Col A — First you need to Format>Cells>Time> 1/35/55 AM/PM (h/mm/ss AM/PM) The Macro understands and drops the AM/PM during conversion
Col B — Second you need to Format>Cells>Custom>00\:00\:00\:00 (or drop frame)
Col C — Third you need to Format>Cells>Number>Number w/no decimal places
Paste your time stamp info in Col A. I am using 12 hour based with AM/PM.
Select Cell B1 and goto Insert>Function>(select all)>timetoTC>select A1 Column cell from helper and that should return the time stamp with frames now added to the end as 00. (eg 20:57:55:00)
Select Cell C1 and goto Insert>Function>(select all) TCtoframes>select
B1 Column cell from helper then fame rate and it should return 2264250
That should work – if it doesn’t check to make sure you’re formatting the columns properly. That’s the most important thing and it may take a while for you to figure it out for your own project. Just noodle it till it works. Nice little plug in saving me lots of effort. Thanks
Schmoe
Yet another comment from my earliier post.
I found what appears to be a rounding error in the formula for converting time to timecode. It could be that the error is introduced because the routine is a base 29.97 fps and I’m using 30 fps. Whatever the reason, when a number on the hour such as 12:00:00 AM was used the formula would convert it to 11:98:99:00 during the conversion. This wreaked all sorts of havoc every few rows and screwed up the frame count but only on even time stamps such as my example. The solution I found which looks like it’s going to work is to change the formula in the formula bar to:
=timetoTC(A2+0.00000001) rather than =timetoTC(A2).
If you’re having this problem try that. Thanks again to the site owner and the person who created this routine. Hopefully my responses here will help pay back their efforts
Schmoe
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.
Does the formula have a limit? I am trying to add up a duration column (I don’t care about in and out, I have duration and need to keep track of on going totals). Right now I should have about 400hrs in total duration but the TCsum is just showing me a standard 01:27:36:03. I am working 25fps.
Any thoughts?
Works like a charm on Mac 10.4.11, Excel 2004. Does NOT work on Mac OS 10.5.8 Excel 2008. I tried everything suggested above (i.e., opt-command-T at the start of workbook, and command-= to refresh). The cell is either -00:00:00:03 or 23:59:59:06. My formula is =TCminus(B6;A6;24). In Excel 2004, I must use =TCminus(B6,A6,24). I tried that in 2008 – no go. I even tried Excel’s formula menu, which includes the TCminus function – no go. Please help! I have a cue sheet that needs finishing yesterday.
Yay! i got it to work with the following:
=TCminus(C21,B21,29.97)
B column is IN time, C column is OUT time for 29.97fps footage.
Excel 2004 on OS 10.4.11
I downloaded and unzipped the file. Double-clicking on it gave me a “this file is not the right format” error. I dragged it into the Microsoft>Office>Add-ins folder. I launch excel 2008, press command+option+t and nothing happens. Nothing at the bottom of my worksheet. Format>Style does not list anything that looks like a TC option. Can you explain what I should do. Speak slowly. Thanks.
Please read Update II in the post body. I cannot offer support for this plugin, nor can I do much else except hope that it works on your machine.
As some people have indicated above, tc.xla will not work with Excel 2008 (as far as I can tell).
Also, the formula instructions indicate semicolons where a lot of users will actually use commas. This is because a comma is a decimal in many countries, so the semicolon is used there instead. If you’re in the US you probably want to use commas.
E.g. “=TCplus(TC1;TC2;fps)” should actually be “=TCplus(TC1,TC2,fps)”
The formula I can’t get to work is TCtoText. I input “=TCtoText(1154325)” and should get “01:15:43:25″ back as a string but I always get “0″.
If anyone has any advice on that function please post here.
Here’s a link for the google-translated instruction page.
http://translate.google.com/translate?js=y&prev=_t&hl=en&ie=UTF-8&layout=1&eotf=1&u=http%3A%2F%2Fwww.belle-nuit.com%2Ftimecode%2Ff.html&sl=fr&tl=en
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!
So close. Great tip to remove the : and ; from the input numbers. Applying the style converted 14624 to 00:01:46;24. But I’m still missing something. Trying to calculate between two columns with “=TCminus(C523;G523)” still scolds me about a formula error. It has to be something obvious or something in the sequencing of the procedure. What did you do to get it working exactly? Any help would save me from calculating 1100 lines of time code by hand.