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.
36 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
Schmoe, you have unlocked it. I had to write to THANK YOU, and to add detail from how I used your excellent tips.
I am using Excel 2010 on Windows 7. I needed a dynamic timecode calculator to add up segments to reach a total running time for a number of acts. This program works like a charm.
1) The Application. I went to http://www.belle-nuit.com/download/index.html. Under the “TC Extension” heading, I downloaded “tc.zip (Version DOS, 5KB).”
2) The Installation. I unzipped the file, opened Excel 2010, went to File/Options/Add-Ins. At the bottom, I browsed to the folder with tc.xla and selected it. It became an activated add-in.
3) The Activation. I opened a new spreadsheet and clicked “Control+T.” I have no idea if this was necessary, but Schmoe is king at this point.
4) The Preparation. I accepted I could not apply this to the existing timecodes in my EDL export. But I knew if I retyped only once, I could start manipulating the data. So I added three new columns to the right of my initial segment time that was in mm:ss:ff format.
5) The Conversion. Next, I followed Schmoe’s tips and to format Column A, but I had to use Format>Cells>Custom> h/mm/ss AM/PM. I then realized I didn’t need this step at all since I already had durations. I just needed the durations, in Column B, to be the right format. Ignored Column A at that point.
Next, in Column B, I formatted it using Schmoe’s Format>Cells>Custom>00\:00\:00\:00 (or drop frame). Then I looked at my original timecode in cell A1, say 03:07:03, and typed it into Cell B1 as 00030703. Voila, the colons appeared, and it displayed as 00:03:07:03.
Next, in Column C, cell C1, I typed in the function =TCtoframes(B1). Voila, it converted it to beautiful framiness. (Note: I did not have to manually type in the function. Instead, I could also go to Formulas/Date & Time and see all of the lovely TC.xla functions right there. That comes in handy next.)
5) The Calculation. I repeated TCtoFrames from Col B to Col C for rows 2, 3, and 4, then I just used “Sum” in row 5 to add up all of the frames in Column C. That gave me a nice giant number. Sure enough, I selected that cell, went back to Formulas/Date & Time, and chose “FramestoTC,” and I got re-converted! There were no colons, so instead of, say, 12:11:04, I got 121104. Believe me, that was fine.
I am sure I could find a function to add those colons back, but given how many dozens of segments I have to add up, I am taking my win and rolling forward.
The great thing is, I see that the magic is in getting the correct conversion to frames. I could use this to sum, subtract, find TC durations, etc. The key is in using the right cell style, then entering the right way, then selecting the TCtoFrames function, then making magic with the frames, then converting from FramestoTC to get it looking presentation ready.
This program is a keeper! Thanks to belle-nuit for creating, to Talino for posting and explaining and to Schmoe for adding the additional detail!
Following dmitcha’s helpful note of Jan 6 2011 I downloaded the macro for Excel (PC) that he cites
http://www.belle-nuit.com/download/index.html. Under the “TC Extension” heading, I downloaded “tc.zip (Version DOS, 5KB).”
dmitcha shows how to install this on Excel 2010. My older Excel 2003 allowed me to add the macro by going to the drop down menu >tools>add in. I browsed for the macro, and added it.
When I open Excel I activate the TC macro with Control+T
I wanted to add up the times of a couple of hundred archival clips, but I didn’t want to have to type in any of the time codes. So I pulled an EDL from my project (B-listing to show clips by source roll). I opened that spreadsheet in Excel. Now my IN and OUT times are in hh:mm:ss:ff format.
Next you format the cells using the Format cell>custom>00\:00:0:0 option. (scroll down to the bottom of the custom options. If you activated the macro using Control+T, it will be there. And you have a choice of drop frame (00:00:00;00 or NDF 00:00:00:00. Notice the colon vs semicolon)
Here’s where I solved the first problem that dmitcha reported. The program won’t work on the data I imported from my EDL because the macro expects to see data without colons. e.g. 00304512. But all my data was imported with colons e.g. 00:30:45:12 dmithca solved this problem by reentering the data without colons. But I had hundreds of entries imported from my EDL, and I’m lazy. So instead of re-entering the data, I did a search and replace function that simply removed all of the colons that came in with the EDL listings. Then when I activated the macro the colons were automatically reinserted by the macro, and I could proceed to calculate the lengths of the clips by converting them to frames, subrating IN from OUT times, and then converting them back to TC. And I can add columns of frames to get the total time from each archival source roll. Brilliant. See dmitcha’s note for more details about how to do that.
Finally, when dmitcha got his total it appeared in the format hhmmssff without any colons. All he had to do was to make sure that the cell was formatted for the custom macro and presto, the results appear with colons.
Many thanks to the team that originally created this neat little marco.
GM
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
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.
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
Here is a little trick that I used to prevent loosing one frame everytime I use TCminus since the duration from 10:00:00:00 – 10:00:00:01 should be 2 Frames the Frame on 10:00:00:00 and the Frame on 10:00:00:01. This is also what the AVID calculator would give out.
TC1 and TC2 are my Timecodecells and AL49 is a cell somewhere formatted as TC and with a 1 as content
This Formula checks if there is a result if there is none it just adds two empty cells if there is a result it adds the content of cell AL49 to the result of
TCminus(TC2;TC1)
=IF(TCminus(TC2;TC1)=0;TCminus(TC2;TC1);(TCplus(AL49;(TCminus(TC2;TC1)))))
to get rid of the ::: when there is no result at all I just used a conditional formatting to change the color of the text to the color of the background.
To get a workaround for the TCsum problems of not reloading the results I just used some extra cells to calculate a provisional result and added the result of the next line to the provisional result.
It works for me, but I can’t explain it any better.
If I only knew how to include this macro to the document so that everyone can use it instantly.
how do you specify framerate in the macro? i am working in 24fps (23.98) and i cannot get the function “TCtoFrames” to return the correct value. and it won’t accept a ;24 anywhere in the function.
any thoughts? thx.
u can email me directly with an answer if you have one! thanks!
Thanks for a very helpful blog post!
For those of you looking for an Excel document to use as a sample, I’ve just uploaded one to my blog <a href="here.
Hope it helps!
Best Regards, Chris!
Thanks for a very helpful blog post!
For those of you looking for an Excel document to use as a sample, I’ve just uploaded one to my blog:
http://blog.latenitefilms.com/2011/08/11/calculating-timecode-in-excel/
Hope it helps!
Best Regards, Chris!
@Tal. Works like a charm. This save me some time! Thanks.
I love this addin, I have a new computer at work and wondering any of you can help.
I have Excel 2007 and I can’t find something I use to use in Excel 2002.
In 2002 I used to be able to go to “Format” => “Style” and then select Timecode style
Now in 2007 I have installed the “Timecode Add In” but I can’t figure out how to get to the pre-defined style to use on cells.
Can you help?
I look forward to trying this. I loose patience with using macros unless they save me lots of time.