Timecode in Excel

[SimonK]

I would like to create a new custom number format that
uses timecode with the option of formatting according to
the 4 types of timecode used: ie

PAL 25 frames per second HH:MM:SS:FF
NTSC 30 Non Drop Frame HH:MM:SS:FF
NTSC 29.97 Drop Frame HH:MM:SS:FF
Film 24 frames per second HH:MM:SS:FF

Ideally having each of these as new custom formats in
which you would only have to enter in the digits for each
of the hours, minutes, seconds, and frames respectively.
Excel would then calculate the actual number of total
frames represented by the timecode and use that number for
all calculations while still displaying according to the
format above.

I've written formulas which can convert between cells by
creating a custom format 00":"00":"00":"00 and then
turning that eight digit number into a frame total once
the base (25, 30, 29.97 or 24) has been determined, and
then another forumla that takes that TotalFrameNumber and
displays it as timecode, but I want to have this all
happen behind the scenes as it were so cell A1 could have

12345432 entered, display it as 12:34:54:32 and then
interpret it as 1132382 frames.

Any pointers in this direction would be greatly appreciated

 

[Harald Staff]

Hi Simon

As you've probably noticed, Excel won't handle those calculations herself. John
Walkenbach's PUP http://j-walk.com/ss/pup/pup5/index.htm supports most/all TC formats, and
there's quite a few VBA solutions out there
http://groups.google.com/groups?as_q=timecode&as_ugroup=microsoft.public.excel.*&num=100&a
s_scoring=d&hl=en

As you may know, 1 is a day in excel, so 12:00:00 is 0.5. 12150015 is tons of years.
Converting it to half a day, 0.5 some, requires serious math, not just formatting. Big
question is then; when you enter 12, do you want it to be12:00:00:00 or 00:00:00:12 ?

 

[SimonK]

Thanks for that... I think the stuff on google groups
might help, and if all else fails I'll stop trying to re-
invent the wheel and get the PUP stuff

Simon

-----Original Message-----
Hi Simon

As you've probably noticed, Excel won't handle those calculations herself. John
Walkenbach's PUP http://j-walk.com/ss/pup/pup5/index.htm

supports most/all TC formats, and