Time code formula for film

[Belerophon]

Hey guys,

I used the formula that solved the problem for this question:

http://www.excelforum.com/excel-gene...l-formula.html

But now i need a formula to add up all the values of the duration column so that i get a result in hh:mm:ss

I would appreciate any help,

Thanks!!!

[DonkeyOte]

Welcome to the Board

I'm sure daddylonglegs himself will look on this and provide a more elegant method but I guess I'd probably opt for something like the below...

NOTE: the below utilises functions DOLLARDE & DOLLARFR - both require activation of the Analysis ToolPak in Excel 2003 via Tools -> Add-Ins.

A1: (onwards) contain Start Times in format of hh:mm:ss:ff
B1: (onwards) contain End Times in format of hh:mm:ss:ff

where ff has a base of 24

I'd probably be inclined to convert Start & End values into Seconds based values in adjacent cells, such that

Please Login or Register  to view this content.

The difference can then be shown in terms of seconds.frames using

Please Login or Register  to view this content.

To then sum the durations (with the base of 24 f per sec), if we assume we have times in rows 1 to 10 we can calculate the total duration in terms of sec.frames using:

Please Login or Register  to view this content.

In terms of displaying the above in hh:mm:ss ... what do you want to do with the frames ? You could show as milliseconds if that's your wish ?

To do so, add a division of 86400 to the above formula and format E11 as hh:mm:ss.00

To reiterate I'm convinced there's a better, more elegant method which dll will provide in due course - hopefully in the meantime the above will help you progress the project ?

[Belerophon]

Thank you!

I've tried this and it seems to be working. I have one question though: When you say I can just add a division of 86400 to the formula in E11, where exactly do I add it?

Thanks again.

[Belerophon]

Hi again, I've been reading your first response and i've realised I have another question!

After including the 86400 division, how do i format e11 so that it shows as *hh:mm:ss.oo*?

Thanks again!

[DonkeyOte]

With E11 active press CTRL + 1 to bring up the format cell dialog, on the Number Tab choose Custom and in Type box enter: hh:mm:ss.00

And just to confirm re: division,

E11: =(existing formula)/86400

[Belerophon]

Thank yo so much!

It works perfectly!

If its not too much to ask i have one last question. How do I make, for example, the content of rows 2+3 the header so that they automatically appear at the top after a page break (without affecting the formulas you've provided me with)??

Thanks!

[DonkeyOte]

Rather than installing multiple headers for the sake of printing why not use "Rows to Repeat" funtionality in your Page Setup ?

[Alan Drum]

Hi,

I am looking for a formula that will convert Gigabytes into hh:mm:ss or viceversa. We are shooting with a "Phantom" camera that records onto a massive hard drive.

I'm guessing the conversion will depend on the speed of the filming and the quality of it, and therefore i would greatly appreciate it if you could provide me with some cells in which this information could be input and so change the output value dpending on which one is used.

Here is a link with the camera specs:

http://www.visionresearch.com/index....=camera_HD_new

Thank you very much

[shg]

Welcome to the forum, Alan.

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.