« Posts tagged time code

Time codes in Excel

I was looking for quite some time to find a practical solution to handle time codes in Excel. There are some solutions out there, but for some reason I just could not find what I needed. I went ahead and programmed my own Visual Basic script. It is nothing fancy, but it does exactly what I need. Here a short list of the functionality:

  • Support for all frame rates
  • Support for drop frame time code (29,97 and 59,94 fps)
  • Converting time code in frame number and vice versa
  • Calculating frame duration from two time code values

This is what I have done so far to get from a time code to a duration in frames:

Time code
IN

Time code
OUT

Number of frames
IN

Number of frames
OUT

Duration in
frames

01:01:11:07

01:01:16:07

88111

88231

119

I used this formula to convert a time code to a frame number in Excel:
=IF(A23<>0;LEFT(A23;2)*60*60*24+MID(A23;4;2)*60*24+MID(A23;7;2)*24+RIGHT(A23;2);0)

This is the formula to calculate the duration in frames:
=(IF(AND(C23<>0;D23<>0);D23-C23;0))-1

The time codes were exported from Avid as an EDL (formatted as CMX_3600). This is the reason why I have to subtract one frame from the duration in frames (the “out” time code of a clip is the same as the “in” time code of the following clip).

This works for me most of the time but it is a little bit a roundabout way. I need two extra columns, which is not really a problem. It is just not the perfect solution if you like everything nice and tidy, like me. Plus these formulas do not work with drop frame time codes.

Now, with the Time Code Tools for Excel, it is just one formula.

Time Code Formulas

Here is a short overview. For more information take a look at the help document provided in the ZIP file.

TC2F

This function converts a time code into a frame number.

=TC2F(A1) or =TC2F(A1;25) or =TC2F(A1;29.97;TRUE)

F2TC

With this formula you can convert a frame number into a time code.

=F2TC(A1) or =F2TC(A1;25) or =F2TC(A1;29.97;1)

FDur

You can use this function to calculate the duration in frames from two time codes.

=FDur(A1;B1) or =FDur(A1;B1;25) or =FDur(A1;B1;59.94;0)

Download

The ZIP file includes an Excel spreadsheet, a help file and the source code.