Timecode for Beginners (Or How I Learned to Stop Worrying and Love Gen AI)

Dealing with timecode is something which everyone has to deal with on a daily basis and calculating it can be a minefield. I used this opportunity to discuss some of the new Gen AI solutions.

One of the best parts of creating a newsletter is hearing back from people who find it helpful in their daily lives. Recently, I received an message from Linde Geerinck, who is currently assisting on a documentary. Linde has several assistant and production coordinator credits and began her career as a runner on Wes Anderson's Isle of Dogs. She wrote: "I'm an assistant editor and really enjoy your Substack! I 100% agree that sharing tips and tricks in the industry is so beneficial, and there's so much about our job that could be less tedious. You probably have your own quick way of dealing with adding and subtracting timecodes (and finding total durations), but just in case, I wanted to share this very simple Google Sheets formula I made a few years ago and still use."

The Problem with Timecodes

Timecode is the clock from which all filmed footage is measured. It consists of hours, minutes, and seconds, with each second divided into a number of frames—single images that exist within one second of footage. Feature films typically use 24 frames per second (fps), while television often uses 25fps or 30fps, depending on whether it is European or American. In recent years, more complex variations like 23.97 and 29.97 have been introduced to allow formats to work "more efficiently" with the many delivery methods now available.

This video does a good job of explaining why these formats exist.

Calculating timecodes (and timings in general) is a complex and often frustrating part of the production process, but it's an unavoidable aspect of the daily work for editors, assistant editors and post production supervisors and coordinators.

Many create these documents in Google Sheets, often building their own code (or sharing it with colleagues) to move between projects. Linde kindly shared a template she built while working on the TV show The Baby.

There are many ways to handle these calculations, but rather than providing a ready-made document, I thought this would be a good opportunity to explore some methods for creating your own functions in Google Sheets. I also want to encourage anyone intimidated by the term "coding" to try out the tools available for building custom spreadsheets. You may find it empowering to create your own solutions and streamline your workflow. It's much easier than it used to be, and I hope to inspire people to take the plunge without fear.

So, set aside a couple of hours this weekend (or whenever you can), create a new Google Sheet, and follow along!

The “Basic” Formula

The easiest way in my experience to calculate timecode is to convert time timecode into frames, to do the calculation and then convert back into timecode.

Frames=(Hours×3600+Minutes×60+Seconds+FramerateFrames in timecode/Framerate )×Framerate

So for the timecode 10:01:12:02 and the framerate 25fps the answer will be 901,802.

This Is Already Giving Me a Headache

I can already imagine many of you glazing over at the thought of this. It may seem complex, but in recent years, several tools have emerged that handle this kind of complexity very well—especially generative AI.

One of the most efficient ways to use tools like ChatGPT or Google Gemini is by asking them to help you write complex code from relatively simple English prompts. In this tutorial, I'll show you how to use AI to "code" a Google document for calculating timecodes.

Building a Timecode Calculator with the Assistance of Chat GPT

CREATE A GOOGLE DOCUMENT

I create this simple document with the following information:

• Cell E2 - Framerate (in this case 25fps)

• Cell D6 - TC_IN

• Cell E6 - TC_OUT

GO TO CHAT GPT

PROMPT 1

In Google Document create a piece of code that takes Framerate (Cell E2), TC_IN (Cell D6 and in the format HH:MM:SS:FF) converts it to frames, TC_OUT (Cell E6 and in the format HH:MM:SS:FF) converts it to frames and then delivers TC_OUT frames minus TC_IN frames. Code this for a cell.

ANSWER 1

You can achieve this by using a custom function in Google Sheets, written in Apps Script. Below is a step-by-step guide to create this script:

Steps to create the script in Google Sheets:

1. Open your Google Sheets document.

2. Click on Extensions in the top menu.

3. Select Apps Script.

4. In the Apps Script editor, delete any default code and replace it with the following:

javascript

Copy code

function framesDifference(framerate, tcIn, tcOut) { // Helper function to convert timecode (HH:MM:SS:FF) to frames function timecodeToFrames(timecode, framerate) { var parts = timecode.split(':'); var hours = parseInt(parts[0], 10); var minutes = parseInt(parts[1], 10); var seconds = parseInt(parts[2], 10); var frames = parseInt(parts[3], 10); // Convert timecode to total frames var totalFrames = (hours * 3600 + minutes * 60 + seconds) * framerate + frames; return totalFrames; } // Convert TC_IN and TC_OUT to frames var inFrames = timecodeToFrames(tcIn, framerate); var outFrames = timecodeToFrames(tcOut, framerate); // Return the difference between TC_OUT and TC_IN in frames return outFrames - inFrames; }

5. Save the script with a name like TimecodeScript.

Usage in Google Sheets:

1. In your Google Sheet, enter the framerate in E2 (for example, 24 for 24fps).

2. Enter the TC_IN timecode in D6 (for example, 01:00:00:10).

3. Enter the TC_OUT timecode in E6 (for example, 01:01:00:10).

4. In another cell where you want the difference in frames to appear, use this custom formula:

excel

Copy code

=framesDifference(E2, D6, E6)

This will calculate the number of frames between TC_IN and TC_OUT based on the framerate provided.

Let me know if you need more help!

PROMPT 2: (GPT gave me a solution which involved building a script in the extensions but I also wanted to also ask if it would give me a line of code that I could add to a google sheet cell that would do the same thing)

Is it possible to do this all in the cell without the extension

GPT ANSWER 2:

Yes, you can perform the calculations directly within a cell using built-in Google Sheets functions without needing to use Apps Script. Here’s how you can do that:

Formula to Calculate Frame Difference

Assuming:

• E2 contains the framerate (e.g., 24).

• D6 contains the TC_IN timecode (e.g., 01:00:00:10).

• E6 contains the TC_OUT timecode (e.g., 01:01:00:10).

You can use the following formula in another cell to calculate the difference in frames:

excel

Copy code

=(((VALUE(MID(D6, 1, 2)) * 3600) + (VALUE(MID(D6, 4, 2)) * 60) + VALUE(MID(D6, 7, 2))) * E2 + VALUE(MID(D6, 10, 2))) - (((VALUE(MID(E6, 1, 2)) * 3600) + (VALUE(MID(E6, 4, 2)) * 60) + VALUE(MID(E6, 7, 2))) * E2 + VALUE(MID(E6, 10, 2)))

Breakdown of the Formula:

1. TC_IN Calculation:

   • VALUE(MID(D6, 1, 2)) * 3600: Converts hours to seconds.

   • VALUE(MID(D6, 4, 2)) * 60: Converts minutes to seconds.

   • VALUE(MID(D6, 7, 2)): Gets seconds.

   • VALUE(MID(D6, 10, 2)): Gets frames.

   • All of these are combined and then multiplied by the framerate from E2 to convert everything to frames.

2. TC_OUT Calculation:

   • The same logic is applied as above for E6.

3. Difference:

   • Subtract the total frames calculated for TC_IN from TC_OUT.

Usage

1. Place the formula in a cell (e.g., F6) to calculate the frame difference based on the values in E2, D6, and E6.

This formula will give you the difference in frames without needing any additional extensions. Let me know if you need further assistance!

Here is a link to the GPT conversation in full.

And here is the resulting document:

BUILD OUT THE CALCULATOR TO PROCESS YOUR WHOLE EPISODE:

The formula which GPT has created here is very simple. It has created what is called a “Function”. Now by typing =framesDifference(E2, D6, E6) it will take frame E2 to mean the fps, D6 to mean the TC_IN and E6 to mean the TC_OUT.

In Google Sheets, dragging the corner of a selected cell downwards allows you to seamlessly auto-fill adjacent cells by extending formulas, patterns, or values based on the initial cell's content. In this case you will have to adjust to make sure that you are always referencing cell E2 for the timecode in all codes but D6 will become D7 will become D8 etc as you pull down.

Now I can add the TC_INs and TC_OUTs from my sequence or list of markers and Google Sheets will do the calculations.

Moving Forward

The great thing about a tool like GPT is that it is only limited by your imagination. While having some knowledge of how code is structured and how to craft prompts is helpful, as long as you have a clear idea of what you want to achieve and the necessary information to do so, your only real limit is your creativity.