If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec)
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec)

#1
July 19th 07, 08:22 PM posted to microsoft.public.excel.misc
 KJ7 external usenet poster Posts: 1
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec)

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)
~kj

#2
July 20th 07, 01:04 AM posted to microsoft.public.excel.misc
 Gary''s Student external usenet poster Posts: 11,058
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec)

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)
> ~kj
>
>
>
>

#3
August 6th 07, 11:16 PM posted to microsoft.public.excel.misc
 Kara7 external usenet poster Posts: 2
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec)

Follow-up to my previous problem, since I forgot to think about the issue of
drop-frame vs. non-drop frame timecode.

Drop-frame time code (primarily used in film projects shot at 24fps)
actually drops 2 frames per second every minute except on minutes ending in
zero (in order to remain in sync). So - it actually ends up being 23.97 fps.

Anyone out there got the math for Excel 2003 to help me out with that?

Here's where I'm at so far: I've got a cell (C13)marked DROP FRAME validated
to be True/false, and H13 is Frame Rate in fps(ex: 24, 30, etc) and G21 is
the TC in ... so I'm hoping that I can set something up where

=if(C13,(text(G21,"hh:mm:ss")&":"&TEXT((G21)*24*60 *60-INT(G21*24*60*60))*H13",00))), (--INSERT DFTC FORMULA HERE --))

Hidden Columns B&D- starting @ row 21: where Columns A & C are TC In & Out:
=LEFT(A21,2)/24+MID(A21,4,2)/(24*60)+MID(A21,7,2)/(24*60*60)+RIGHT(A21,2)/(H13*60*60*24)
=LEFT(C21,2)/24+MID(C21,4,2)/(24*60)+MID(C21,7,2)/(24*60*60)+RIGHT(C21,2)/(H13*60*60*24)

Hidden Column G - starting @ Row 21:
=D21-B21

Thanks Again!!
Best ~
KJ7

"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)
> ~kj
>
>
>
>

#4
October 6th 07, 02:35 PM posted to microsoft.public.excel.misc
 Gervin Callo[_2_] external usenet poster Posts: 3
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/s

this is helpfull.... pls i need one but in (frames = 25 frames/s

ty

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

#5
October 8th 07, 11:49 AM posted to microsoft.public.excel.misc
 Gervin Callo[_2_] external usenet poster Posts: 3
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/s

hi! please convert this to > 25 frames per second (pal video) ty

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

#6
October 8th 07, 01:42 PM posted to microsoft.public.excel.misc
 David Biddulph[_2_] external usenet poster Posts: 8,651
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/s

Well you could look at the formula and work out what it is doing (as all the
functions arte standard Excel functions which are clearly explained in Excel
help), or at the very least you could look at where 30 appears in the
formula and wonder whether you could sensibly replace the 30 by 25 to meet
your needs. And of course you'll test it, as you would with any other
formula which is suggested to you.
--
David Biddulph

"Gervin Callo" > wrote in message
...
> hi! please convert this to > 25 frames per second (pal video) ty
>
> "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
>>
>>
>> >

#7
February 10th 10, 10:49 PM posted to microsoft.public.excel.misc
 Matt[_7_] external usenet poster Posts: 2
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
> >
> >
> >
> >

#8
February 12th 10, 03:25 PM posted to microsoft.public.excel.misc
 Matt[_7_] external usenet poster Posts: 2
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
> > >
> > >
> > >
> > >

#9
December 1st 16, 09:28 PM posted to microsoft.public.excel.misc
 external usenet poster Posts: 1
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec)

Hi there, do you have a template excel sheet for this?

Cameron

On Thursday, July 19, 2007 at 8:04:01 PM UTC-4, 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
> >
> >
> >
> >

#10
December 2nd 16, 04:07 PM
 Hslebbng030 Junior Member First recorded activity by ExcelBanter: Dec 2016 Posts: 12

Quote:
 Originally Posted by KJ7 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
I'd always want to be update on new posts on this internet site, saved to favorites!

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Need Help with Freezing/Splitting 2 Frames in Excel 2003 Tyn Excel Discussion (Misc queries) 3 February 13th 07 07:19 PM Frames in Excel with drop down boxes. Adam Excel Discussion (Misc queries) 2 December 31st 06 05:53 PM freezing frames Bob Griendling Excel Worksheet Functions 4 July 1st 06 10:58 PM Calculation of Hrs and Mins from 2 Time Frames Corey Excel Worksheet Functions 6 May 31st 06 05:12 PM how do I add/subtract time when I'm going from PM to AM (ex. 11:4. tammyj Excel Worksheet Functions 1 March 15th 05 08:31 PM

All times are GMT +1. The time now is 01:08 PM.