Thread: Formula for subtracting and adding timecode

Hi,

I'm working with a large volume of timecode in both 25fps and 30fps (29.97 to be exact).

It looks like this:
(Column A) (Column B) (Column C)
00:00:11:07 00:00:13:08
00:00:14:03 00:00:15:11
00:00:16:15 00:00:17:19
00:00:18:18 00:00:20:03
00:00:22:07 00:00:25:10

I need to subtract Column A from Column B to get the "duration" for each set.
Then, at the end, I need to add up all the duration timecode in Column C to get the "total duration" all in the same format, meaning HH (hour):MM (minute):SS(second):FF(frame).

I would appreciate it if someone could show me what each formula would be for getting the "duration" AND "total duration" and in different frame rate.

THANK YOU!

I have created a mock worksheet.

Each cell in Column A and Column B will have HH (hour):MM (minute):SS(second):FF(frame).

I need the duration (Column C) which should be Column B minus Column A as well as the total duration after all the individual duration is calculated.
I highlighted the cells that I would like to get the results for in yellow.

This sample timecode is 24 fps (but I sometimes need to change it to 25 or 30 fps).
In the 24fps format, the frame count goes up to 23 and it switches to 24.
In 25 it goes up to 24, while it goes up to 29 in 30 fps.

Thank you so much for your help

I'd suggest using a single cell to store the fps value, eg:

F1: 24
(25, 30 etc...)

Then:

C2:
=("0:"&TEXT(DOLLARFR(ROUND(((LEFT(B2,8)*(86400*$F$1)+RIGHT(B2,2))-(LEFT(A2,8)*(86400*$F$1)+RIGHT(A2,2))),0)/$F$1,$F$1),"0.00"))+0
copied down to C35
format as hh:mm:ss.00

for total duration

C37:
=SUMPRODUCT(FLOOR($C$2:$C$35,"0:1.0"))+("0:"&TEXT(DOLLARFR(SUMPRODUCT(ROUND(MOD($C$2:$C$35*86400,1)*100,0))/$F$1,$F$1),"0.00"))
format as hh:mm:ss.00

as you alter F1 so the results should adjust

Note: the above utilises DOLLARFR function which pre XL2007 requires the Analysis ToolPak Add-In be activated (Tools -> Add-Ins)

Attached is a working version of the above
Note: columns I:M simply reflect a "proof" table and are not used in the calculations taking place in C

The above is just one approach - there will be others - no doubt more elegant.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

I found the following functions online. Could you kindly take a look at the attached sample file and provide me with the formula for the total duration (orange cell)? I would appreciate it very much.

30FPS

=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/86401,"m:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),30),"\:00")


24FPS
=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/86400,"m:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),24),"\:00")