Calculating Timecode in Google Sheets

One of the most popular blog posts we’ve ever done has been on Calculating Timecode in Excel. Even though it’s now around 3 years old, it still gets a constant amount of views each week.

Although the Excel article is still relevant and useful – personally, I don’t actually use Microsoft Excel very often any more, much preferring Apple’s Numbers (which comes free with every Mac; and iPad/iPhone for that matter). Unfortunately however, getting timecode to work successfully and reliably in Numbers has never been something I’ve been able to fully accomplish. Hopefully one day someone puts together some incredibly clever AppleScripts that add this functionality, however in the meantime…

Google Docs is a free, web-based office suite offered by Google within its Google Drive service – allowing you to create and share your work online and access your documents from anywhere. As part of that suite, Google Sheets is basically Google’s version of Excel/Numbers. It’s web-based, but there are also iOS and Android versions that all play nicely together. It’s also completely compatible with Excel and Numbers – and it’s free.

What’s REALLY exciting though, is that the incredibly clever Stockholm-based Henrik Cednert has put together an awesome script (inspired by Nuke/Hiero Timecode Calculator) that allows you to calculate timecode within a Google Sheets document. It’s early days, and the code does not currently support obscure non-even frame rates (i.e. 23.976fps) – but apart from that, it’s incredibly simple and powerful, with support for timecode addition, subtraction, division, multiplication, percentages as well as timecode conversion (i.e. 24fps to 25fps). If you’re in 25fps land, this is an incredibly useful tool that you can make use of today. For assistant editors who have to spend a lot of time crunching timecode within a spreadsheet – this could save you a HUGE amount of time.

As Google Sheets is cloud-based, this is nothing something you can just download and install – you need to have a Google Account, and then “Make A Copy” of Henrik’s document, storing your own version in your personal Google Drive account. Once you make a copy of the document you can modify it to your heart’s content – although if you do make any useful changes to the code (i.e. if you add non-even frame rate support), I encourage you to share them with Henrik so that the whole post community can benefit.

You can access the sheet here, which contains some examples as well as basic instructions on how to use the document.

HUGE thanks to Henrik for sharing this great tool!

Happy Calculating!

18 Responses

  1. Ramon
    Jul 03, 2014 - 02:00 AM

    Hi Cris,

    That is fantastic. works fine. I did not have experience with scripts, and i was looking for something like that to use TC a long time do Google docs. I only find a barrier, taht my spreadsheet have a lot of rows and after some point, google show this error…

    “Script invoked too many times per second for this Google user account”

    I looking for a solution, and google only inform that. ” This indicates that the script began executing too many times in a short period. It most commonly occurs for custom functions that are called repeatedly in a single spreadsheet. To avoid this error, code your custom functions so that they only need to be called once per range of data, as explained in the guide to custom functions.”

    I tryied to undertand how to optimize the script but I dont have skills to do that. So I would like to let this challenge here for somewhelse. Hope someone can find a solution. For now I just use excel to give me the number I didn’t need to calculate everytime to avoid use the script so many times.

    thanks for it! amazing work

    best,
    Ramon

    Reply
    • NEO
      Jul 03, 2014 - 06:51 AM

      Oooh! Will take a look at that. Would it be possible to share that doc with me? Or can you create a dummy doc that looks the same? My email was in that original sheet. =)

      Reply
      • Ramon
        Jul 05, 2014 - 03:04 AM

        Hi Neo, I share with you my chart -and you will see the error happening. It’s not a issue in your code, but some limitation that Google put in account users. I imagine that maybe it’s possible edit something that avoid the function be call several times and avoid that. unfortunately I dont have skills to do that. I also thinking after that some calculation dont need be dynamic, so I can keep only the result and avoid call the function many times. Also, I would like to if it’s not borrow you, Is ask with in some update If you could add a function “add” that SUM a interval of cells. That would be a great help ex: SUM(D5:D67), but using your TC script. something like that “= tc( D5:D67, 24, “add”)

        thanks a lot
        Ramon

        Reply
        • NEO
          Jul 06, 2014 - 11:21 PM

          So, there’s really no workaround to this with the current approach of evaluating the function per cell. I did a special thing in Ramon’s case where I created a new function called ‘tcrange’. With ‘tcrange’ you feed the function ranges instead of cells. It will calculate all those ranges at once and return a new range with the result.

          There are upsides and downsides to this but I will look at rewriting the script so that it works with ranges out of the box. If someone is in urgent need of it, drop me a line and I’ll toss you the experimental script. =)

          Ramon’s case also made me think that it would be sweet if you could feed it with a large range and add all those TC’s together to get a total duration. The problem I’m facing now though is that it will be hard to keep it simple and clean when there’s so much that it could be used for and so much functionality that could be added.

          Reply
          • Chris Hocking
            Jul 06, 2014 - 11:44 PM

            This may or may not be helpful – but what I used to do in Excel was convert all my timecode values to frames, then I could use normal Excel functions to do basic calculations (such as sum’s), then convert the final result back to timecode. I would normally have one cell as my master timecode rate that all the timecode calculations referred back to. Now that Henrik has added the ‘2f’ and ‘2tc’ functions, you could do the same thing.

          • NEO
            Jul 07, 2014 - 07:51 PM

            There was no reply-button on your post Chris so I had to reply one post up. Confusing. =)

            What you say is basically what the script does. It converts everything to frames, does the math and then back. If the script was used for the “2f” and “2tc” operations in Ramon’s case he would get the same error since it needs to call the function for all those cells. The only workaround that I can find is to lift all of as a range and return it as a range. A big upside is also that it’s like 5000 times faster. =)

            Shouldn’t be that hard to kiss my tc-function and the workaround tcrange-function together into one. Will look into that and also add some other stuffs.

  2. NEO
    Sep 21, 2014 - 10:28 PM

    Better late than never… The experimental tcrange set up for Ramon have now been combined into the normal tc-function. Have updated the docs and examples in the original spredsheet, http://goo.gl/bzvwWU .

    Documentation is always hard so let me know if something is confusing or if you have any q’s. Will look at support for odd frame rates and drop frame support next.

    Reply
    • Russell
      Sep 02, 2015 - 06:38 AM

      Thank you!
      Question: if I want to add up all the cues used to get a grand total, as in this spreadsheet…
      https://docs.google.com/spreadsheets/d/1y1bh78Mclq8aWHDQDtMl4t48k2NChBykagBmsoG2HzU/edit?usp=sharing
      where I want to add up all the “DURATIONs” for a grand total on that column
      Thanks!!!
      Russell

      Reply
      • Chris Hocking
        Sep 02, 2015 - 08:57 AM

        Neo might have a better suggestion, but an easy way to do it would convert your duration cells to frames, then add up all the frames, then convert that back into timecode.

        Reply
  3. Sam Ellens
    Dec 04, 2015 - 05:40 AM

    Thanks. This just saved me a ton of time trying to use Avid’s terrible TC calculator to calculate the correct Aux TCs to sync a consistent audio/video offset.

    Reply
  4. Ludvig
    Apr 01, 2016 - 09:35 PM

    Is it possible to convert the other way around? From 25fps (source tc) to 24fps (target tc). That would be extremely helpful!
    Best regards, Ludvig

    Reply
    • Chris Hocking
      Apr 03, 2016 - 01:50 PM

      Yes, to quote the documentation, the “conv” function will convert tc1 to the framerate given as tc2. tc2 in this case should be target framerate as an integer (e.g. “00:12:30:11, 25, 24, “conv”).

      Reply
  5. Thomas
    May 28, 2016 - 04:32 AM

    So, I may be missing a big thing here but one difference between the excel and google versions is the use of timecode as a numeric field. In Google sheets, I’m having trouble figuring how to work with timecodes entered as numbers (without the “:”). It returns the aN:aN:aN:aN. Basically, I want to format input cells as timecodes, so when I enter 12345678 in A1 it displays 12:34:56:78, This is doable. But then I want to use that cell in a tc formula, and get some sort of result. Am I missing something??

    Reply
    • Thomas
      May 28, 2016 - 06:01 AM

      I think I figured out a work around. I know there’s gotta be a simpler way but:

      Instead of A1 being inserted into the final formula I just us the TEXT function in its place, (TEXT(A1, “00:00:00:00”). So, instead of the original addition function it becomes:

      =tc((TEXT(A1, “00:00:00:00”)),(TEXT(B1, “00:00:00:00″)), 24,”add”)

      Now I can format A1 and B1 as regular 8 digit numbers that are displayed as 00″;”00″:”00″:”00

      I would love to know if there is a better way to get the same results but this will work for now.

      Cheers

      Reply
  6. Carl
    May 10, 2017 - 03:05 AM

    Hi,

    Have you had any luck setting up an option to calculate a column of timecodes into a final TC? I’m absolutely dying for a function like this.

    Best

    Reply
    • Chris Hocking
      May 10, 2017 - 10:14 AM

      I’m not exactly sure what you mean? Do you just want to add up a whole column of timecode values into a “total” cell? If so, yes, this is already possible – check out the examples.

      Reply
  7. Darren Monk
    Jul 27, 2017 - 05:17 AM

    Is there any way to remedy the error message:

    Service invoked too many times in a short time: exec qps. Try Utilities.sleep(1000) between calls. (line 0).

    I assume the suggestion of putting Utilities.sleep(1000) between calls. (line 0). somewhere in the script might help but I can’t for the life of me figure out how to put it in. Every time I try the error persists so I can only assume I’m putting it in the wrong place.

    Reply
  8. Gert Driesen
    Aug 02, 2017 - 10:24 PM

    Hi,

    There is also a way to convert TC to Frames and Frames to TC with a Excel / Google Sheet formula.
    See: https://docs.google.com/spreadsheets/d/1mL11PLKFFniDH-0z8t43aVim1HLps6BW-BjYMASTG54/edit?usp=sharing

    Greetings.

    Reply

Leave a Comment