Calculating Timecode in Excel

UPDATE (26th January 2017): As Brad mentions in the comments below, you can now download the TC.XLAM 2.0 beta, which is no longer password protected and works on modern versions of Excel. For those that no longer use Excel, there’s also a Google Sheets version explained here.


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.

1. Download the XLA script from here (I hope you don’t mind Matthias, but I’ve also mirrored it here – just in case your site ever goes down).

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:

=TCtoframes(A1,25)

9. And do the same for B2:

=TCtoframes(A2,25)

10. Now add B1 and B2 together:

=B1+B2

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:

=framestoTC(B3,25)

12. And assuming everything was typed in correctly – you should now have your finished calculation!

There is a list of all the different functions you can use here, although there’s a more detailed explanation on the French page (which you can use Google Translator or similar to translate).

However, most functions don’t really need much explanation, so here’s a summary of what’s available:

  • TCtoframes(TC,fps)
  • framestoTC(frames,fps)
  • TCplus(TC1,TC2,fps)
  • TCminus(TC1,TC2,fps)
  • TCmult(TC1,facteur,fps)
  • TCdiv(TC1,facteur,fps)
  • TCsum(TCref,fps)
  • TCtrans(TC,fromfps,tofps)
  • TCtofeet(TC,gauge,fps)
  • feettoTC(feet,gauge,fps)
  • TCtometer(TC,gauge,fps)
  • metertoTC(meter,gauge,fps)
  • timetoTC(intime)
  • TCtotime(inTC)
  • texttoTC(intext)
  • TCtotext(inTC,fps)
  • isTC(TC,fps)
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!

41 Responses

  1. Youssef
    Sep 28, 2011 - 02:47 PM

    I keep getting stuck on TCminus(TC1,TC2,24). It gives me the error

    Macro Error
    Names cannot look like references
    Macro error at cell [TC.XLA]TC source corr1!E2

    Im just trying get a duration of a shot subtracting one time code from another. I’ve tried using colons, semi colons, without the FPS and with. All to no avail. Any ideas would be great!

    Reply
    • Chris Hocking
      Sep 28, 2011 - 03:20 PM

      I would suggest just converting everything to frames first, doing the calculations, then convert back to FPS at the very end (as per the example I gave).

      In your case, just follow the instructions on the blog, but at step 10, change the plus to a minus.

      Hope that helps!

      Best Regards, Chris!

      Reply
  2. Bob Daly
    Dec 08, 2011 - 09:31 PM

    I’m losing my mind. I’m doing EVERYTHING you have written here – the exact order you have it written, I’m using EXCEL 2008 on OS 10.6 and it WILL NOT work…. It recognizes the functions(they list when I type in an equals sign), I’m pressing Comm Opt T, I see the website at the bottom … NOTHING?!
    Am I retarded? I took computer programming in Engineering. I can’t be THAT stupid! What am I missing?

    Reply
    • Chris Hocking
      Dec 08, 2011 - 09:40 PM

      We are also using Microsoft Excel 2008 on a Mac running 10.6.8 – so that shouldn’t be an issue.

      If you followed the instructions exactly – then it should just work.

      Try exiting Excel, then starting again. Make sure at Step 5 that you select ALL the cells you want to be used for timecode values.

      Also, try downloading my example. It probably won’t actually work (as for some strange reason Excel won’t import the XLA script properly on sheets you don’t create yourself) – but you can use it to check that the syntax is the same.

      Good luck!

      Reply
  3. Chris D.
    Jan 06, 2012 - 11:54 AM

    Hi thanks for the post it helps explain it a lot better than the website.

    A question though… does anyone have a macro or port of this for Mac Pages ? I really do not want to go back to working in excel! Thanks for any updates and answers to my question.

    Reply
    • Chris Hocking
      Jan 06, 2012 - 08:22 PM

      Unfortunately I don’t know of any similar solution for Pages. If you find one – please let me know!

      Best Regards, Chris!

      Reply
  4. Al
    Oct 18, 2012 - 10:06 PM

    Hi Chris, thanks so much for that, couldn’t get it going from the other sites’ instructions. However, I was wondering if you could double check a weird result I get. When I do a ‘TCminus’ (using 25 fps) on 07:21:01 minus 06:55:01 I get 2600 frames, which is obviously not right. 26 seconds yes, which equals 650 frames. Do you get this result too?? It worked fine for me with smaller intervals though.

    Reply
  5. stefany
    May 04, 2013 - 03:02 PM

    Hi Chris, I’m unable to get this to open with my version of Excel (Excel for Mac 2011, v14.0.0). When I drop the “TC.XLA”, a window pops up, and it says:

    “Microsoft Excel 5.0/7.0 opening TC.XLA”
    (with a progress bar under it)

    However, nothing happens, b/c immediately, this window pops up:

    “This file contains Visual Basic Macros. The macros that have been disabled. If you save it, the file will contain the Visual Basic project”

    There’s an option to click OK, which I did, and then I get another window:
    “This workbook contains defined names that conflict with valid cell references. To correct this issu, underscores (_) have been added to the defined names that conflict with the cell references. Conflicting names in XLM macro code have not been changed, and muc be corrected manually.”

    Again, there’s an option to click OK, and when I do, the “Microsoft Excel 5.0/7.0 opening TC.XLA” window disappears, and then nothing happens.

    So … basically, I can’t get it into my Excel. Any tips you can offer would be greatly appreciated. I have a list of thousands of shots (TC/in and TC/out), and I JUST found out that I need to indicate the duration of each shot. There HAS to be a way, but I can’t figure it out!! :(

    Reply
  6. Chris Hocking
    May 04, 2013 - 05:26 PM

    Hi Stefany,

    Sorry – I’ve only tested this on MacOS 10.6.8 and Excel 2008.

    Excel 2011 includes specific settings that help you control how users are notified about potentially unsafe macros. On the Excel menu, click Preferences. Under Sharing and Privacy, click Security, and then deselect the Warn before opening a file that contains macros check box, and see if that helps.

    This should hopefully allow you to at least open the document. You then need to press Command+Option+T as per the instructions above for the scripts to activate.

    However, if that doesn’t work – if you only want to calculate the duration between a start timecode and end timecode, and you have a consistent frame-rate, then you could quite easily put together a formula to do that.

    This might be of some help:

    http://www.renegrasser.com/393/time-codes-in-excel/

    Good luck!

    Best Regards, Chris!

    Reply
  7. stefany
    May 05, 2013 - 06:53 AM

    Chris –
    Thank you so much for the quick response! I appreciate the help & will give this a try!
    -Stefany

    Reply
  8. Dwayne R. Brown (@eckaiguy)
    Aug 09, 2013 - 01:44 AM

    This is magic. Thank you Chris for putting this together. It took me a couple of months trying to wrap my head around working with TC in Excel and looking at other suggestions. This is the one that works. Kudos to your source (Matthias) as well. Thank you again!

    Reply
  9. Kayla
    Jan 30, 2014 - 11:19 AM

    Hi Chris!

    I’m trying to use this macro, and I am able to get up to step 5 before it all falls apart. When I go to modify style, it won’t allow me to change the style name (it’s grayed out).

    I’m using OSX10.9, Excel 2011 14.3.9. Any suggestions, or is the macro just not suited to the latest version of Excel? For the record, I also tried to use the formulas but they also do not work.

    Any suggestions would be a major help …! Thanks :)

    Reply
  10. Victoria
    Feb 01, 2014 - 03:52 AM

    Hi!

    The macro worked perfectly for me until I saved the document and reopened it again. All formulas that worked before closing are giving me an error message if I try to drag them down and continue with my data calculations. If I re-enter the formula it works again. …weird.

    Any advice?

    Much appreciated! :)

    Reply
    • Chris Hocking
      Feb 01, 2014 - 12:58 PM

      What version of Excel Victoria? Are you pressing COMMAND+OPTION+T after Excel first opens the document?

      Reply
      • Victoria
        Feb 06, 2014 - 02:50 AM

        Excel 2010. I pressed control+t (I’m using windows) when first launching the macro like it said in the description. Its strange, it only seems to malfunction when I save and close the file I’m working on. I save it as a Macro-Enabled Workbook. Still seems to throw it off…

        Reply
        • Chris Hocking
          Feb 06, 2014 - 09:28 AM

          Sorry – I haven’t tested it on Excel 2010 on Windows.

          Reply
  11. James Manser
    Jun 09, 2014 - 09:36 PM

    That is so cool……. Even I could get it to work. Much appreciated :-)

    Reply
  12. Darren
    Sep 27, 2014 - 11:26 AM

    Can I use this method to do the following:

    Take my timecode and convert it into Frames. CHECK.

    Use the frames to add or subtract a certain value to create a new framecount.

    Convert the new framecount back to timecode.

    Basically, I have an excel document with an entire film worth of subtitle timecode on — and the client went ahead and changed a logo at the front and the duration is different, therefore every single subtitle timecode after the change comes 1 second and 21 frames later than originally.

    Is there a way for excel to take my original timecodes and create a column of new timecodes that are 1 second and 21 frames later?

    Reply
    • Chris Hocking
      Sep 27, 2014 - 12:38 PM

      Absolutely! The above example should be all you need.

      Reply
  13. Darren
    Sep 27, 2014 - 01:27 PM

    Okay I’ll give it a go. Wasn’t sure which part was the part where I change the frame number from, say 10 frames to 25 frames (i.e, add 15 frames to all fields on excel)

    Reply
  14. Darren
    Sep 28, 2014 - 03:44 AM

    I can’t seem to paste a timecode value into the cell and it work. Does this method only work if you type the TC out like 01000122? If I paste 01:00:01:22 from a document where I already have TC written i just get the VALUE error.

    Reply
    • Chris Hocking
      Sep 29, 2014 - 01:19 PM

      Hi Darren,

      Sorry, unfortunately I don’t have Excel on me to double check the best way to tackle this.

      Have you tried setting the existing timecode values in your spreadsheet to the “Timecode Style” as explained in the blog post? If that doesn’t work, you might have to do a “search and replace” to get rid of the existing colon’s before you change the style. This should only take a few clicks, and solve your issue.

      FYI – you can also do calculations in Google Sheets: https://latenitefilms.com/blog/calculating-timecode-in-google-sheets/

      Hope this helps!

      Best Regards, Chris!

      Reply
  15. Ben Mills
    Oct 08, 2014 - 01:05 AM

    Hi Chris,

    I used to be able to use the TC extension and use all of the functions perfectly, but now for some reason it wont work?

    I’m running on Excel For Mac 2011 14.2.0.

    I’ve tried to start from scratch but on import of the macro initially I get a dialogue box giving me the following options – Enable Macros / Do Not Open / Disable Macros

    I assume Enable Macros is the correct one?

    I then get an error saying the following: ‘This Workbook contains defined names that conflict with valid cell references’

    I hit ok and then start on the document. I hit CMD+Opt+T which shows the macro has loaded in the status bar. This is further clarified by the Timecode and Timecode DF styles appearing in the format window on the Home bar.

    I then manually type in a 24 fps timecode into A1 and =TCtoFrames(A1,24) into A2, this then gives me a #VALUE? error

    Any ideas?

    Kind Regards

    Ben

    Reply
    • Chris Hocking
      Oct 09, 2014 - 12:59 PM

      Sorry Ben – I’m not sure. Have you tried re-downloading the Excel document and starting from scratch?

      You could also try resetting the Excel preferences (http://support2.microsoft.com/kb/975724)?

      Reply
    • Tim Warner
      Feb 26, 2015 - 03:51 AM

      Hi Chris,

      I’m having the same issues as Ben Mills except that when I hit” Command Option T” basically nothing happens. Timecode does not pop up as an option in the format/style menu. It basically seems like the macro has failed to copy over. I’m running 10.8.5 on this Mac and Excel 2011 v. 14.4.7

      Reply
  16. Darren
    Oct 10, 2014 - 11:22 AM

    This stopped working for me yesterday. Same as Ben – I get #VALUE! even after using this thing flawlessly all week. Column A is set to TC, and I put =TCtoframes(A1,24) into B1 and #VALUE!

    I’ve reset preferences and downloaded a few new TC.XLAs from original and mirror site.

    Reply
  17. Ben Mills
    Dec 10, 2014 - 09:57 PM

    I’ve tried using it again and still no joy……. I’m using an un updated version of office too! Really annoying as I have seen it work :(

    Reply
  18. Alex
    Feb 06, 2015 - 11:43 AM

    Hey Chris,

    I am currently trying to convert TC into Frame count for over 800 different TCs. Is there a way I can use =TCtoframes but have it calculate all of the timecodes to frame count in one big go? For example something like this =TCtoframes(A1:A800,24)

    So far I’ve had no luck doing it like that, however my knowledge in excel is limited. Any advice/wisdom?

    Great work btw!

    Alex

    Reply
    • Chris Hocking
      Feb 06, 2015 - 11:52 AM

      Hi Alex,

      I would just have the time code in one column, the ‘TCtoframes’ in the next column, then at the bottom of this column do a ‘SUM’ to add up all the frame numbers together. You can then convert that ‘SUM’ into timecode if you need to. This is pretty much exactly the same as my example.

      You can easily drag formula’s across multiple rows and columns:

      http://www.extendoffice.com/documents/excel/867-excel-apply-formula-to-entire-column-row.html

      Good luck!

      Best Regards, Chris!

      Reply
  19. Jon
    Aug 26, 2015 - 04:29 PM

    Hi Chris,

    I’m not so familiar with Excel formulas, but the formula only worked for me when I put “;” instead of “,”.
    So for me it does not work like this: =TCtoframes(A1,25)
    It does work like this instead: =TCtoframes(A1;25)

    I don’t know what’s the difference. Just wanted to leave a comment so it could maybe help anyone with the same problem.

    Thank you for this post!

    Jon

    Reply
    • Chris Hocking
      Aug 26, 2015 - 04:44 PM

      Thanks Jon!

      Reply
  20. Brad Loflin
    Jan 26, 2017 - 08:07 AM

    I have used this XLA for years. Why in the world would you convert to Frames first? It is a robust TC calculator. Add or subtract all day long in Timecode at any framerate, drop-frame or not. For example your simple addition above would be:

    What you see What is there Explanation
    00:01:30:00 13000 This is A1 with TimeCode Style applied
    00:01:30:00 13000 This is A2 with TimeCode Style applied
    00:03:00:00 =TCplus(A1,A2,30) Formula but cell has TimeCode Style applied

    You can Sum, Minus, Multiply, Divide, Plus and more.

    Reply
  21. Brad Loflin
    Jan 26, 2017 - 08:10 AM

    That was not so clear after posting. Try this:

    Cells A1 thru A3 Formatted
    00:01:30:00
    00:01:30:00
    00:03:00:00

    Data in A1 thru A3
    13000
    13000
    =TCplus(A1,A2,30)

    Reply
  22. Brad Loflin
    Jan 26, 2017 - 08:16 AM

    Here is another one:

    00:01:30:00
    00:01:30:00
    00:00:30:00
    00:00:45:00
    00:01:25:00
    00:01:45:14
    00:01:25:22
    00:08:51:06

    013000
    013000
    003000
    004500
    012500
    014514
    012522
    =TCsum(G1:G7,30)

    Reply
  23. Brad Loflin
    Jan 26, 2017 - 08:21 AM

    Last one. Let’s say the producer wants to do 15 1 1/2 minute interviews. How much time is that? 22:30

    00:01:30:00
    00:22:30:00

    1300
    =TCmult(A12,15,30)

    Reply
  24. Brad Loflin
    Jan 26, 2017 - 09:16 AM

    I hope Chris Hocking doesn’t mind but I will post a few notes I have migrated from workstation to workstation concerning the TC.xla over the years. I have lots of notes but I will only put a few here. The original TC.XLA has been update to work with modern version of Excel and should be used. It is TC.XLAM (Good discussion about the difference between XLA and XLAM:
    http://stackoverflow.com/questions/13071185/xla-vs-xlam-addin-what-is-the-difference). TC.XLAM can be found here http://www.belle-nuit.com/tc-xla.

    Through the years the add-in was always hidden and password protected. Now the new xlam version is not. So you can view and edit the VBA code directly and create something of your own from it, maybe a Pages friendly version for Macs. The code was released as open source so shouldn’t be any legal issues in using it that way. I actually wrote some of my on code back in the day that did much of what TC.xla did but it is nice to see the efficient elegant way it was written as mine was not elegant.

    For Windows, to get it to work you must check it in the Add-Ins widget on the Developer tab and have Macros Enabled. If not available in the Add-ins Widgit hit browse there for it, note the default location for your user add-ins, then in another window copy it there, then back in the Add-in window add it and check it active. The Styles should show up on the Styles list on the Home ribbon, use the little drop down arrow to see all styles. If not there go to Quick Access Toolbar under Options and find the Macro “AddTimecodeStyle” and add it to toolbar long enough to run it once. All it really does is format a cell(s) to 00\:00\:00\:00 or 00\:00\:00\;00 which you can do yourself under Format Cell – Custom.

    Through the years I have used the TC add-in on various Forms that pop-up and interact with the user so the user could enter IN and OUT time and get DUR or IN and DUR and get OUT, etc. Also used it lots for running times for ROS (Run of Show) and other things like that.

    If you always use 30 frames per second or 29.97 you can hard code that is so you don’t have to enter it every time. I think the default is 25 fps. You could use a reference to a hidden frame holding your fps value, something like that.

    Hope you enjoy using it. If you get it working for Pages or Google let us know as everyone is moving away from the old tools it seems.

    One last time I found that is wonderful for TC is this:
    http://current360.com/apps/timecode-calculator/tc.html

    I’m sure most of you have used this but if not, your are welcome. It is awesome and gorgeous and always just a click a way on any device. I use it all the time.

    Brad

    Reply
  25. Chris Hocking
    Jan 26, 2017 - 10:30 AM

    Thanks for your detailed notes Brad – very much appreciated!

    I can’t actually remember why you originally needed to convert to frames first – I think there was some kind of bug where some of the functions weren’t correctly taking into account the frame-rate.

    Great to see that the new version is Open Source – VERY helpful!

    Reply
  26. Steve
    Mar 25, 2017 - 04:51 PM

    Nice post. After typing a value into two cells (a1 & a2). I did a straight subtraction (=a2-a1) and got the correct result. No need to convert, calculate, reconvert, at least not for this task.

    Reply

Leave a Comment