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!!!!!!!!!

HT


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
=TEXT(B2/86400+D2+0.000104,"hh:mm:ss:")&TEXT(C3,"00")

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

=TEXT(B2/86400+TIMEVALUE(LEFT(D2,8))+0.000104,"hh:mm:ss:")&TEXT(C3,"00")

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
=TEXT(C$1,"hh:mm:ss")&":00"

In c4 enter the formula
=TEXT(C$1+(SUM(B$3:B3)+9*(ROW()-3))/86400,"hh:mm:ss")&":"&IF(LEN(TEXT(ROUNDDOWN(F4/60,0)-ROUNDDOWN(F4/600,0),0))>1,TEXT(ROUNDDOWN(F4/60,0)-ROUNDDOWN(F4/600,0),0),"0"&TEXT(ROUNDDOWN(F4/60,0)-ROUNDDOWN(F4/600,0),0))
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.

HTH