Timecode Calculator

July 5, 2005 10:27 AM

Timecode filter: how do I subtract a given number of frames from 400 timecodes in an Excel spreadsheet?

Video timecodes are in the following format (for NTSC non-drop frame) - hh:mm:ss:ff. The ff represents frames, of which there are 30 per second (00 - 29). I want to subtract 5 frames from 400 different timecodes, all of which are less than a minute long.

The best I've come up with so far is removing the hh:mm using find and replace (because all the video clips are under a minute), then using the LEFT and RIGHT Excel functions to separate the seconds and frames into individual columns, then multiplying the seconds by 30 to give their length in frames, adding the seconds column to the frames column to give total frames and then subtracting 5.

This is as far as I've got - I'm not sure if this is the best method, and I don't know how to return the total frames to the original hh:mm:ss:ff format (including that single digit seconds and frames require a leading 0 added).

I've Googled all over the place and whilst there are several timecode calculators available, they only do individual calculations as opposed to batches. And yes, I've tried www.belle-nuit.com - unfortunately the Excel macro doesn't work correctly on current versions of Excel, and the Filemaker Pro plug in appears to only do individual calculations.

I have Excel 03 on XP Pro, and easy access to Macs if required. Any ideas? Thanks.

posted by forallmankind to computers & internet (5 comments total)

Probably your best bet is to create a User-Defined Function (UDF) that will give you the number of frames in a given timecode. This way you can put something like "=FramesInTimeCode(A1)" in a cell, then auto-fill that down. You can use these UDFs just like other formulas, so you can do things like removing 400 frames easily, if you also made a =TimeCodeFromFrames()

I'll see what I can do.
posted by odinsdream at 10:54 AM on July 5, 2005

Okay, so here's something that converts a timecode into total frames (i think, double check my math at the end):

Function NumFrames(TimeCodeCell As Range)
Dim Hours As Integer
Dim Minutes As Integer
Dim Seconds As Integer
Dim Frames As Integer

TimeCodeText = TimeCodeCell.Value

Hours = Mid(TimeCodeText, 1, 2)
Minutes = Mid(TimeCodeText, 4, 2)
Seconds = Mid(TimeCodeText, 7, 2)
Frames = Mid(TimeCodeText, 10, 2)

NumFrames = Frames + (Seconds * 30) + (Minutes * 30 * 60) + (Hours * 30 * 60 * 60)
End Function

Go to the visual basic editor (Alt+F11), go to Insert > Module, and paste this code in. Now, return to your worksheet, and in a cell next to one of your timecodes, type the formula "=NumFrames(A1)" where A1 would be the cell containing your timecode. The result should be total frames.

Now, er, the next part would be to make the reverse function, going from what I have above, and make it give you a timecode once it has a number of frames. This would be a simple matter of dividing over and over putting the results into hours, minutes, seconds and frames. Unfortunately my algebra sucks, and I could never convert decimal to binary very well (only binary to decimal), which is much like what this would be.
posted by odinsdream at 11:07 AM on July 5, 2005

Er, and you'd finally end up with something like:

=ToTimeCode(NumFrames(A1)-5)
posted by odinsdream at 11:09 AM on July 5, 2005

So you've got a column of 400 corrected framecounts, let's call it F1:F400.

Set the formula for G1 to =CONCATENATE("00:00:",TEXT(trunc(f1/30,0),"00"),":",TEXT(mod(f1,30),"00")).

Fill it down to G400.
posted by fleacircus at 11:18 AM on July 5, 2005

alright - you guys ROCK!!! I'd been pulling my hair out on that one all week - thanks!
posted by forallmankind at 5:22 PM on July 5, 2005