Video timecode question

[=?ISO-8859-1?Q?Ar=E8al_Tosser?=]

Hello,

I am a video editor and am trying to figure out how to get Excel 2000 to
calculate the difference between two frames of video...

Let me explain: all frames of digital video have a timecode which is
expressed as the SMPTE value and is shown as hh:mm:ss:ff where ff are
frames (25 frames per second in PAL, 30 frames per second in NTSC).
I want to take a value such as 00:32:43:10 and subtract 52 seconds and 5
frames from that (00.00.52.05) and get Excel to show me the result.
I have tried and tried and got nowhere on this, and am not sure why: now
I am appealing for your help!

Firstly, how do I format cells so that Excel can recognise this
hh.mm.ss.ff format? I can start with the
format->cells->number->special->hh:mm:ss but then what? Or should I be
looking at format->cells->number->time?
Secondly, how would I then subtract one time from another?

Any help much appreciated

Cheers

Marc

remove .invalid to reply

 

[Harald Staff]

Hi Marc

You can not format Excel to make her understand that a decimal is 1/24 or
1/25 or 1/30, so what you need is a new set of functions.

I believe John Walkebnach's PUP can do this
http://j-walk.com/ss/pup/pup5/index.htm
and there are custom VBA solutions out there. Do a Google search on
Excel Timecode

HTH. Best wishes Harald

 

[=?ISO-8859-1?Q?Ar=E8al_Tosser?=]

Thanks for your reply Harald,

Do you think that if I ignored the frames value, so that I was merely
trying to subtract one time in the format hh:mm:ss from another Excel
would be able to do that? If so, how would I go about doing it?

Have searched Google extensively, but have failed to find exactly what I
am looking for.

Many thanks

Mark

remove .invalid to reply

 

[Harald Staff]

Sure. Simply subtract time as you do with any numeric value, no problem.
Excel has a problem with negative time though, and you will encounter that
if you have time in your entry cell and the exit cell is blank. So do
something like
=IF(B1>A1,B1-A1,0)

HTH. Best wishes Harald