Subtracting SMPTE timecode

**jayduerrmusic** · 08-13-2008, 05:48 PM

In the film music business we synchronize music to film frames by a standard known as SMPTE timecode. A frame of film is labeled by hr:min:sec:frames.
For instance a picture starting a 4 hours 8 sec would have SMPTE timecode 04:00:08:00. If we are working at 24 frames per second the difference between 01:01:10:10 and 01:01:09:20 would be 14 frames. In other words 01:01:10:10=01:01:09:34 minus 01:01:09:20=00:00:00:14.

How can I set up a cell so that if I type in 01223304, 01:22:33:04 would appear denoting SMPTE timecode. Then I would like to subtract one cell from the other to get the correct difference in min:sec:frame.

I would also like to be able to switch between 30 frames/second and 24 frames/sec formats. For instance, At 24 fps 01:01:10:10 minus 01:01:09:20 =14 frames. At 30 fps 01:01:10:10 minus 01:01:09:20 =20 frames.

I am an excel novice and don't even know where to begin.

**rylo** · 08-13-2008, 07:37 PM

Hi

Try this formula approach

A1: FPS
B1: 24

A4: 01010920, Format, Cells, Custom, Type: 00":"00":"00":"00
A5: 01011010, Format, Cells, Custom, Type: 00":"00":"00":"00
B5: =IF(RIGHT(A5,2)<RIGHT(A4,2),RIGHT(A5,2)+B1-RIGHT(A4,2),RIGHT(A5,2)-RIGHT(A4,2))

This should give you the correct result for your example using both the 24fps and 30fps.

However, I'm not sure it will be right for other situations. Can you give more examples and the expected outcomes if you can't work out how to change the formula from this example.

HTH

rylo

**jayduerrmusic** · 08-13-2008, 08:25 PM

Thanks Rylo:

I inputed the info and it seemed to work okay for both 24 and 30 fps until i tried to input a number that would produce a difference of Min:sec:frames.

For instance: A4 01000800
A5: 01011010
B5 should read 1:02:10 but instead I get 10

Also If I could make B5 round out the frames to the nearest second that would be helpful. For instance: 1:02:10 = 1:02

The purpose of this is to make a cue sheet for film music. I could then input start times (A4) and End times (A5) then this could give me the length of the music cue in min:sec (B5).

Unfortunately I am ignorant on formulas such as the way you came up with this. Any books or links that could help me understand this proceedure?

Much appreciated,

jay

**rylo** · 08-13-2008, 08:29 PM

Jay

Yea I wasn't at all confident that the formula would work for other situation.

Can you build a workbook that has enough examples to cover most things (maybe 20 or so....), and show the expected result for both 24 and 30, and explain how the calcs should work to get the expected result.

I'll have another look and see if I can work things out from the extra data.

rylo

**jayduerrmusic** · 08-13-2008, 09:08 PM

Can I attach an excel worksheet on this forum? I have a cuesheet already built.

**rylo** · 08-13-2008, 09:22 PM

Jay

If the workbook is under 100k then when you do your reply to the post, either click the paperclip icon or scroll down the page and select the manage attachments button. This will guide you through the attachment process.

If the workbook is over 100k, then use WinZip to zip the file. Either way it has to be < 100k.

rylo

**jayduerrmusic** · 08-13-2008, 10:14 PM

Thanks. Cuesheet is attached.

**rylo** · 08-13-2008, 11:47 PM

Jay

Can you please update the example file to show how each and every one was calculated.

By my workings, the intime of 01 00 03 16 and outtime of 01 03 08 23 with a fps of 24 the output is 4447 and is calculated [(3*60+5)*24+23-16 - being 3min5seconds (185sec by 24 fps = 4440) plus the difference between 23 and 16?

IF this is correct, insert the code below into a general module in the workbook.

Please Login or Register  to view this content.

Now in G7 enter the formula =calcfps(E7,E8,$C$4).

Another way using inbuilt functions would be
G7: =TIMEVALUE(SUBSTITUTE(LEFT(E8,8)," ",":"))-TIMEVALUE(SUBSTITUTE(LEFT(E7,8)," ",":"))
H7: =(HOUR(G7)*60*60+MINUTE(G7)*60+SECOND(G7))*$C$4+RIGHT(E8,2)-RIGHT(E7,2)

rylo