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
