Timecode Calculations Part 2

Posted by Hugh Taylor on October 10, 2001 2:08 PM

Thanks all for your replies, especially to Eric - your formulas work great - but now we have to figure in the "loss of frames" in drop frame timecode additions...

the formula would need to add 2 frames everytime column c (the running total) passes a minute mark, except for every 10 minuites (10, 20, 30, etc.) where it does nothing...

for example: we start all our compilation reels at the 1:00:00:00 (one hour) mark... at 1 hour and 1 minute, the next available edit point is at 1:01:00:02 because as the timecode passes the 1 minute mark, it adds 2 frames (in reality it "drops" 2 frames)

so again, the columns would look like this:
(there is 9 seconds between each spot)

start time: 1:00:00:00

description length start time
spot1 10 1:00:00:00
spot2 10 1:00:19:00
spot3 10 1:00:38:00
spot4 10 1:00:57:00
spot5 10 1:01:16:02
spot6 10 1:01:35:02
spot7 10 1:01:54:02
spot8 10 1:02:13:04
spot9 10 1:02:32:04
etc, etc...

I used :10's here, but I'll be using :60's, :30's, 20's, :10's, and :05's....

thanks again!!

Hugh

Posted by IML on October 10, 2001 2:41 PM

I still may be way off base but...
let say you fill out the first line spot 1 in line 2 in cols a-C. enter it as
spot 1 10 1:00:00:00
line 2 your would enter
spot 2 10 (or whatever) and this formula in C3
=TEXT(TIMEVALUE(LEFT(C2,7))+B2/86440+0.000104,"h:mm:ss:")&TEXT(MID(TEXT(TIMEVALUE(LEFT(C2,7))+B2/86440+0.000104,"h:mm:ss:"),FIND(":",TEXT(TIMEVALUE(LEFT(C2,7))+B2/86440+0.000104,"h:mm:ss:"))+1,2)*2,"00")

This reproduces your results. Hope that helps.

Posted by IML on October 10, 2001 2:45 PM

Aah, this doesn't address skipping 10s etc. Time to go home! nt

Posted by Eric on October 11, 2001 4:37 AM

what happens when the frame count hits 30?

do you add another second and rezero the frame count?

Posted by Eric on October 11, 2001 6:01 AM

If the count does not rezero after 30, then try this

Posted by IML on October 11, 2001 6:47 AM

Last guess

Okay, in cell A2, list spot 1, B2, list length, C2 list the start time of the first in H:MM:SS format. D2 calulate the frame with the formula
=MINUTE(C2)*2-((MINUTE(C2)>9)+(MINUTE(C2)>19)+(MINUTE(C2)>29)+(MINUTE(C2)>39)+(MINUTE(C2)>49))*2
E2 combines them with =IF(ISBLANK(A2),"",TEXT(C2,"h:mm:ss:")&TEXT(D2,"00"))
Copy down columns D and E as far as you need to.
In C2, put the formula
=C2+B2/86440+0.000104 and copy that down as far as you need.
You can hide columns C and D if you'd like.

This is possible in one cell, but the formula gets extremely long testing the time against all the minutes

Posted by Hugh Taylor on October 11, 2001 9:10 AM

BINGO!

This works now! Thank you very much for all your help!

Hugh

you guys rule!!

Posted by IML on October 11, 2001 10:23 AM

Improvement?

Okay now that the calculation appears to be right, we can clean it up a little.
For this put your start time in E1 in HH:MM:SS format.
In A2 list spot 1, B2 is running time as whole number. C2 is the formula
=TEXT(E1,"hh:mm:ss:")&TEXT(MINUTE(E1)*2-SUM((MINUTE(E1)>{9,19,29,39,49})+0)*2,"00")
You need to hit control shift enter to have this array formula work.
a3 would be spot 2, B3 would be running time, and the formula for C3 and to copy down is
=TEXT(TIMEVALUE(LEFT(C2,8))+B2/86400+0.000104,"hh:mm:ss:")&TEXT(MINUTE(TIMEVALUE(LEFT(C2,8))+B2/86400+0.000104)*2-SUM((MINUTE(TIMEVALUE(LEFT(C2,8))+B2/86400+0.000104)>{9,19,29,39,49})+0)*2,"00")
you also need to hit control shift enter with this one.