+ Reply to Thread
Results 1 to 14 of 14

Time Code Formulea Needed

  1. #1
    Registered User
    Join Date
    09-08-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Excel
    Posts
    5

    Time Code Formulea Needed

    Hi All

    In my job I add and subtract timecode number all the time. At present I have to do it all manually (with the help of a timecode calculator) but this leads to mistakes due to the amount of numbers having to be entered manually.
    I'm looking for 2 formulae to crack this.

    The first is subtraction of 2, 8 digital numbers in a row:
    EGs
    01:00:00:00 01:00:01:00 00:00:01:01
    01:04:00:00 01:06:20:02 00:02:20:03

    The second formula is the addition of the third column. Any number of these need to be added together from 2 to 12 separate 8 digit numbers.

    The solutions needs to be formulae of some description that can be easily changed from 25 frames per second to 30 or 24.

    I know there are some formulae out there but they all seem to not get the addition or subtraction right.
    There is a frame 00 and this needs to be counted a frame. And when 25 frames are counted this needs to advance the seconds by one. There is no frame 25 (in 25fps) and no frame 24 (in 24 fps)

    Thanks in advance

    Tim

  2. #2
    Registered User
    Join Date
    03-08-2016
    Location
    kuala lumpur malaysia
    MS-Off Ver
    2010
    Posts
    92

    Re: Time Code Formulea Needed

    hi, it will be good if you can attach a worksheet for clearer picture.
    the worksheet shall show the source data and the intended data (where the formula that you want to incorporate)

  3. #3
    Registered User
    Join Date
    09-08-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Excel
    Posts
    5

    Re: Time Code Formulea Needed

    Hi Wanmuhd
    This posting system won't let me attach anything.
    I think it's a Mac problem. Best I can do is a screen shot. (I think)

    Screen Shot 2019-10-16 at 9.53.24 pm.png

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: Time Code Formulea Needed

    below would, I think replicate your expected results, where G1 holds fps (24,25,30)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the above cells should all be format as hh:mm:ss.00

    if you want to keep the fps format you'd need to convert to TEXT, again, keeping as "time" makes D4 a little simpler, but can be adjusted for, as necessary (in line with D1 etc)

    if the above doesn't cater for your various requirements, I suspect it won't, post a sample file catering for multiple scenarios using GoAdvanced -> Manage Attachments when replying
    (the paperclip icon feature does not work)

  5. #5
    Registered User
    Join Date
    09-08-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Excel
    Posts
    5

    Re: Time Code Formulea Needed

    Sorry it's been to get back to this thread. That doesn't seem to work.
    I'll try and attach the workbook to see if that helps.

    TIA

    Tim
    Attached Files Attached Files

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: Time Code Formulea Needed

    works for me, per attached

    note: I applied the fps integer in A5
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-08-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Excel
    Posts
    5

    Re: Time Code Formulea Needed

    Hi XLent
    You're brilliant. You have no idea how long I've been wanting this solution.
    There is only one problem with your solution. The column addition of the minutes field in D5 is out.
    I've attached a simplified addition to highlight the problem. D1 should equal 00:00:07:00.
    It's also not that easy to move around. I already broke it once. When I copied and pasted the formula to another field it stopped working.
    Could you be kind enough to give me a small lesson in how to move it in to an existing spread sheet.

    Cheers
    Tim
    Attached Files Attached Files

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: Time Code Formulea Needed

    Hi, can you explain how you arrive at 00:00:07:00 ? But, yes, I suspect it may need a tweak...

    In terms of moving it -- the reference are:

    for the row values: B1:C1 represent start & end values; A5 is the fps integer
    if the start & end dates, per row, are not adjacent in real-life, e.g. B1 & Z1 you can replace reference to B1:C1 with CHOOSE({1,2},B1,Z1)

    for the aggregate value: D1:D3 are the row values; and A5 the fps integer
    if the rows to aggregate are not contiguous you could use a CHOOSE to account for each cell - e.g. CHOOSE({1,2,3},D1,G1,G2)

    if in doubt, post a more complex sample that better illustrates your real-life setup, so to speak.

  9. #9
    Registered User
    Join Date
    09-08-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Excel
    Posts
    5

    Re: Time Code Formulea Needed

    Iíll get a real life example up over the weekend.

    As for the final duration calculation. 2 seconds + 2 seconds + 3 seconds = 7 seconds.
    I stripped away any frames to make it easier to see any errors. The other positions seem to add correctly.

    Thanks for all your help.

    Tim

  10. #10
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: Time Code Formulea Needed

    Sorry, I misread this morning... and thought you were referring to the first line !

    I suspect / hope that the addition of a Round will address the aggregate anomaly

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    basically, the way this approach works is to take the (now) time values in D1:D3 and multiply them by 86400 to convert to seconds as integer -- so 00:00:01.05 become 1.05
    once done it converts the decimal remainder of that value to a base 100 equivalent using the fps value as "original" base, so on 24 fps that 1.05 would be converted to 1.20833 (i.e. 1+5/24)
    once it's aggregated the base 100 balances these can be converted back to base 24 - e.g. 3.20833 -> 3.05 (edit: and then /86400 to convert back to time 00:00:03.05)

    the issue, without the above ROUND in place, was that the decimal seconds were coming in at say 1.99999... and that converted to base 100 from base 24 -> 4.999 etc (1+99/24 etc)
    with the ROUND in place we handle seconds to .000 milliseconds, which is fine, and this would ensure 1.99999 is handle as 2.000, and thus remains 2.000 when converted to base 100 etc.

    hope that's not too confusing - made sense to me when I typed it... but it's been a long day!
    Last edited by XLent; 11-07-2019 at 04:26 PM.

  11. #11
    Registered User
    Join Date
    11-19-2019
    Location
    Vancouver
    MS-Off Ver
    Online
    Posts
    5

    Re: Time Code Formulea Needed

    Hi XLent,
    I am looking for help in a similar way as Timbenol, but only the first formula, (ie. calculating duration per Segment)

    This formula you provided is useful, however I am getting a result that is many decimal places (10+!)

    Is there a way to add the ROUND into the SUM formula? If so where would I put it?
    =SUM(INDEX(DOLLARFR(DOLLARDE(SUBSTITUTE(B1:C1,":",".",3)*86400+{0,0.01},$A$5),$A$5)*{-1,1},0))/86400

    Also, I am curious what the number 3 represents (ie. (B1:C1,":",".",3))

    Thank you!

  12. #12
    Registered User
    Join Date
    11-19-2019
    Location
    Vancouver
    MS-Off Ver
    Online
    Posts
    5

    Re: Time Code Formulea Needed

    SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
    Example
    SUBSTITUTE("abcdefg", "cde", "xyz", 1)
    Summary
    Replaces existing text with new text in a string.
    text_to_search
    The text within which to search and replace.
    search_for
    The string to search for within text_to_search.
    replace_with
    The string that will replace search_for.
    occurrence_number - [optional]
    The instance of search_for within text_to_search to replace with replace_with. By default, all occurrences of search_for are replaced; however, if occurrence_number is specified, only the indicated instance of search_for is replaced.

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: Time Code Formulea Needed

    Hi, so I don't get told off by the Mods can you raise as a new thread and link to this?

    Re: decimals: Time is a decimal value so ensure the cell holding the result is formatted to [hh]:mm:ss.00 [refer post#4]

    The 3 tells SUBSTITUTE to replace the third instance of colon with decimal, so 01:00:00:00 becomes 01:00:00.00 -- XL can treat the latter as a Time value (once coerced), and perform standard arithmetic operations with it (per above - i.e. Time is decimal - so 12 hours = 0.5)

  14. #14
    Registered User
    Join Date
    11-19-2019
    Location
    Vancouver
    MS-Off Ver
    Online
    Posts
    5

    Re: Time Code Formulea Needed

    OK! I am new to this so just figuring out how to post a new Thread.

    The substitute of colon for decimal is applicable in my case as well...still getting results in the 10s of decimals and wonder how to make this into a number that is more similar to a readable amount of frames and seconds.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUMIFS formulea results $0.00
    By GMCbris in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-22-2018, 12:42 AM
  2. [SOLVED] formulea for adding top 4 numbers out of row of six
    By rowneyg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2015, 08:37 AM
  3. formulea for adding top 4 numbers out of row of six
    By rowneyg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2015, 08:36 AM
  4. Code needed big time!
    By oberon.black in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2005, 06:05 PM
  5. [SOLVED] filter list of text for unique entries using formulea
    By Domenic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] filter list of text for unique entries using formulea
    By The Nephalim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. filter list of text for unique entries using formulea
    By The Nephalim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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