View Single Post
  #7   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

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