+ Reply to Thread
Results 1 to 9 of 9

Time code formula for film

Hybrid View

  1. #1
    Registered User
    Join Date
    11-08-2009
    Location
    Christchurch NZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Red face Time code formula for film

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

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel time code formula for film

    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

    C1: =IF(A1="","",DOLLARDE(SUBSTITUTE(A1,":",".",3)*86400,24))
    copied down and across to D as required
    The difference can then be shown in terms of seconds.frames using

    E1: =IF(COUNT(C1:D1)=2,DOLLARFR(D1-C1,24),0)
    copied down as required
    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:

    E11: 
    =DOLLARFR(SUMPRODUCT(INT($E$1:$E$10))+DOLLARDE(SUMPRODUCT(MOD($E$1:$E$10,1)),24),24)
    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 ?

  3. #3
    Registered User
    Join Date
    11-08-2009
    Location
    Christchurch NZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Excel time code formula for film

    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.

  4. #4
    Registered User
    Join Date
    11-08-2009
    Location
    Christchurch NZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Excel time code formula for film

    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!

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel time code formula for film

    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

  6. #6
    Registered User
    Join Date
    11-08-2009
    Location
    Christchurch NZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Excel time code formula for film

    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!

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel time code formula for film

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1