MrExcel Publishing
Your One Stop for Excel Tips & Solutions

timecode calculations

Posted by Hugh Taylor on October 09, 2001 3:06 PM

I need help building an Excel spreadsheet that would list a number of TV spots, their lengths, and calculate a running timecode based on the spots and their lengths. I work in drop frame timecode which basically drops a frame (30 frames per second) every minute, except every 10th minute. In addition, there will be 9 seconds of space between each spot (for black and slate)

the finished spreadsheet would look like this:
and would calculate the "Start Time" as new spots were added to the list...

Description Length Start Time
spot 1 :20 1:00:00:00
spot 2 :30 1:00:29:00
spot 3 :20 1:01:08:02
spot 4 :10 1:01:37:02

Timecode is Hours:Minutes:Seconds:Frames

Thank you for any help!!!!!!!!!


Posted by IML on October 10, 2001 6:26 AM

I think I'm totally lost here, but that's never stopped me before.
Lets say you have spot 1 filled out. Spot 2 is listed in A3. In B3 is time in seconds (enter whole numbers only). C3 is number of frames.
In D3, your start time might be able to be calculated by

I'm not sure if frames should be calculated, can you provide a little more detail on it?

Posted by Eric on October 10, 2001 6:29 AM

about the frame drops

From your description, it sounds as if a frame represents 1/30th of a second, should that be added or subtracted from the start time to next spot. Im confused by your suggested output of "2" for frame drop between spots 2 and 3, and 3 and 4.

Posted by IML on October 10, 2001 6:33 AM

This won't work anyway, nevermind...(nt)

Posted by IML on October 10, 2001 6:37 AM

Quick fix for still broken formula


this should eliminate the value error when copying down past the first row.

Posted by Eric on October 10, 2001 9:47 AM

Is this close to what you want?

say col A is "spot", col B is length in seconds (entered as whole numbers, "20" not ":20") and col C is start time
The first thing I did was add another row at the top, so that row 2 has the column labels and the data starts in row 3
Then, in C1 I put the start time, the time for the first spot, which in your example is 1:00:00 WITHOUT the extra :00 for frames- this makes c1 a true "time" value.

Then in c3 enter the formula

In c4 enter the formula
and copy down as far as you need it.

I'm not sure what you wanted to happen with the frame drops, so I just counted them. They're not affecting the time count, although they are reported in the requested format.

I assumed that the "no drop at 10 minute" feature cycled- that is also no frame drop at 10 min intervals (20 min, 30 min, etc.), and adjusted the count accordingly.