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

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)

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

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

Formula:

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)

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

works for me, per attached

note: I applied the fps integer in A5

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

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.

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

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!

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.

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)

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.