- 21 February 2009 by Chris HockingFinal Cut Studio 3 Predictions
- 11 August 2011 by Chris HockingCalculating Timecode in Excel
- 10 May 2009 by Chris HockingAvid vs FCP – My thoughts…
- 8 March 2012 by Chris HockingFilmapalooza 2011
- 18 November 2011 by Chris HockingSPAA Conference 2011
- 20 February 2013 by Nicholas CollaTropfest: The Rock Show of Film Festivals
- 19 May 2013 by Michael ShanksKeeping up with the Comstocks
- 25 April 2013 by Guest BloggerThe OceanMaker
- 13 April 2013 by Chris HockingImporting AVCHD Footage into Avid
- 9 March 2013 by Nicholas CollaAWG Screenwriters Conference – Part Three
- 4 March 2013 by Nicholas CollaAWG Screenwriters Conference – Part Two
- 24 February 2013 by Nicholas CollaThe Oscars 2013
Calculating Timecode in Excel
Posted: 11 August 2011 by Chris Hocking
Today I was contacted by a filmmaker friend that wanted to work out a way to calculate timecode within an Excel spreadsheet. Fortunately there are some great Macro’s out there that allow you to do just that, however the one I recommended is not terribly well documented, so I thought I would jot down some notes here.
The tool I recommended was Excel Time Code Extension by Matthias Bürcher. It hasn’t been updated since 1997, however it still works great, even on the later versions of Excel. Although there is some great documentation floating around on the Internet (this blog is probably the best resource – especially the comments), when I first downloaded it all I really wanted was a Installation Guide and an Example.
Here’s a crash course for Mac users out there – although the workflow is very similar for PC users. I tested this on MacOS 10.6.8 and Excel 2008.
2. Unzip the download, and then drag the XLA file into Excel.
3. You will be presented with a Macro Warning – just click Open.
4. You will now be left with a blank Excel workbook. To activate the plugin, press Command+Option+T (Windows users can just press Control + T I’m told, although I haven’t tested it personally yet). In the bottom status bar you should see a message like the below to confirm that everything is working properly:
5. You can now start getting to work! Select the fields you want to use as timecode fields, then click Format > Style from the top menu bar.
6. Then select Time Code from the Style Name and click OK.
7. You can now enter timecode into the fields you selected previously. Enter it as a full number, without any spaces (for example 01013000). Excel will automatically format it nicely (i.e. 01:01:30:00).
8. With that complete, you can now start doing some calculations. For the purposes of this demonstration, let’s just add A1 and A2 together. There’s probably a much quicker way to do this, but the most reliable method I’ve found is to convert the timecode values to frames, do the calculations as frames, then convert the frames back to timecode at the end. So, in B1 let’s display the value of A1 as frames (at 25fps). To do this type:
9. And do the same for B2:
10. Now add B1 and B2 together:
11. Now let’s use the cell A4 to show our final calculation. The first thing you need to do is change it to a timecode style (as per step five and six above), then you can enter the following calculation:
12. And assuming everything was typed in correctly – you should now have your finished calculation!
However, most functions don’t really need much explanation, so here’s a summary of what’s available:
Finally, here is an nice and simple example Excel sheet you can download and test for yourself. It’s basically exactly the same as the above example, except laid out a bit nicer. Hope it helps!