View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Matt[_7_] Matt[_7_] is offline
external usenet poster
 
Posts: 2
Default How do I subtract time where hh:mm:ss:ff (frames = 30 frames/s

I used these formuls to create another type of form. What I call a rundown.
What it is, is a sheet to help you calculate segment lengths for a video or
film project of a specific length. So you enter the duration of parts of the
program and it keeps a running total of how long the project is and how much
remains to fill.

Example, you want to produce a 30 minute magazine style show. Segment 1 is
one story, Segment 2 is an interview etc etc...

What I've done is used these formulas to convert the hh:mm:ss:ff timecode
values into seconds and convert them back once I've added or subtracted the
values as needed to tell me how much time has been used up and how much time
is left.

What I noticed is that when I enter the length of the first segment, if the
timecode has a frame value of 15 or higher, the formula seems to add a second
to the time. Ex: My title sequence for a show might be 00:00:45:22 but when
it gets converted into hh:mm:ss.00 and then back into hh:mm:ss:ff the time
becomes 00:00:46:22

This seems to happen even if I don;t do any other operations to the values
other than the conversion. Any ideas?

This works great!

I found one little glitch. Not sure how to fix it.

When the duration of a clip is an even second, for example a clip that is
exactly two seconds long, the results are displayed as: 00:00:01:30

Which is equivalent to two seconds, just like writing two halves equals one.
But it would be more clear if it was displayed as: 00:00:02:00.

Again not sure what the best way to fix that is, just thought I'd raise it.


"Gary''s Student" wrote:

This is based upon 30 frames per second (Digital video)

In A1 and A2 we enter as text:

01:11:27:03
01:11:23:20

In B1 and B2 we enter:

=LEFT(A1,2)/24+MID(A1,4,2)/(24*60)+MID(A1,7,2)/(24*60*60)+RIGHT(A1,2)/(30*60*60*24)
=LEFT(A2,2)/24+MID(A2,4,2)/(24*60)+MID(A2,7,2)/(24*60*60)+RIGHT(A2,2)/(30*60*60*24)

and format as Custom hh:mm:ss.00 to display:

01:11:27.10
01:11:23.67

the tenth of a second because 3 frames is a tenth of a second. In B3 enter:

=B1-B2 to display 00:00:03.43 in the same format. Finally to convert the
.43 seconds into frames, in B4 enter:

=TEXT(B3,"hh:mm:ss") &":" & TEXT((B3*24*60*60-INT(B3*24*60*60))*30,"00")
to display:
00:00:03:13

--
Gary''s Student - gsnu200734


"KJ7" wrote:

I've got a template I'm using (in Excel 2003) where I need to subtract two
time-based fields from one another. (Seems simple enough). However... this is
for use @ a small post production co., where the smallest unit of measure is
not actually the more commonly referenced 'second', but rather - the 'frame'
(generally at the rate of 24 or 30 frames per second).

What I'd like to accomplish is this: a formula that takes the two timecodes
and subtracts in from out... leaving me with a duration:

EX: 01:11:27.03 - 01:11:23.20 = 00:00:03.13 (or 3 sec & 13 frames)
thanks for your help.
~kj