Forum Discussion
Film Frame Calculations
I am trying to work out how to sum a film frame calculation.
For example:
17:40:45:07
12:02:34:17
05:54:28:19
these are hours:minutes:seconds:frames
24 frames makes a second in this scenario (alternatives are that 25 or 30 frames make a sec)
setting the custom cell to hh:mm:ss:ff doesn't work and it always seems to revert to the time and when I attempt a formula to sum the numbers it never works. Any help would be greatly received!
Hi Pamela,
Could be array formula
=SUMPRODUCT(IFERROR(LEFT(D2:D22,8)*1,0))+SUMPRODUCT(IFERROR(RIGHT(D2:D22,2)*1,0))*$F$2/24/60/60
where number of frames is in F2
Hi Sergei,
Thanks for that formula - I've changed the sheet a bit and its not quite working so I'm wondering if you could have a look at this version? Also I'm trying to point some of the figures into the tabs prior including adding up partial days so I've added in a line to calculate. I managed to add a few things manually through a TC calculator I found online (https://current360.com/apps/timecode-calculator/tc.html) but it stops when it hits over 100 hours. Wasn't quite matching up. Anything your able to shed a light on would be helpful. Thanks in advance.
Hi Pamela,
If present result with frames formula will be more complicated
=TEXT(SUMPRODUCT(IFERROR(LEFT(D3:D9,8)*1,0))+INT(SUMPRODUCT(IFERROR(RIGHT(D3:D9,2)*1,0))/$F$2)/24/60/60,"[hh]:mm:ss") & ":" & TEXT(INT((SUMPRODUCT(IFERROR(RIGHT(D3:D9,2)*1,0))/$F$2-INT(SUMPRODUCT(IFERROR(RIGHT(D3:D9,2)*1,0))/$F$2))*$F$2),"00")
(array formula)
To sum only two values that's regular formula
=TEXT(LEFT(H10,8)+LEFT(H27,8)+INT((RIGHT(H10,2)+RIGHT(H27,2))/$F$2)/24/60/60,"[hh]:mm:ss") & ":" & TEXT(INT(((RIGHT(H10,2)+RIGHT(H27,2))/$F$2-INT((RIGHT(H10,2)+RIGHT(H27,2))/$F$2))*$F$2),"00")
In attached file there is a small difference with calculator you mentioned. In calculator if enter the time with frames like 20:42:16:10 it shows 20:42:16:11, i.e. adds one frame to each entered value. Don't know is that by design or bug.
In the attached file calculated results are on the right from your ones.
Pamela Harvey-White A friend had the same issue and asked for my help to calculate the duration between two cells in hh:mm:ss:ff (ff being the frames) format. I've created a formula that does the trick with precise results. It first subtracts hh:mm:ss and then adds the frames. If the subtraction between frames is less than 0, it subtracts a second and then adds the frames. Then it trims the text to ensure negative values aren't preceded by a minus sign.
This thing is not allowing me to attach files, so I'll just paste the formula here. In a table which has the starting time in cell A2 and the ending time in cell B2, it would go like this, with the duration in C2:
A2: 00:02:08:19
B2: 00:02:37:18
C2: =IF((RIGHT(B2,2)-RIGHT(A2,2))<0,TEXT(LEFT(B2,8)-LEFT(A2,8)-1/86400,"HH:MM:ss")&":"&RIGHT(TEXT(24+RIGHT(B2,2)-RIGHT(A2,2),"00"),2),TEXT(LEFT(B2,8)-LEFT(A2,8),"HH:MM:ss")&":"&RIGHT(TEXT(RIGHT(B2,2)-RIGHT(A2,2),"00"),2))
The result in C2 should be 00:00:28:23Hello,
I would like to thank you for the formulas you've created. They saved me a lot of my time.
I was wondering whether there is any formula which can find clip based on the name, count both durations and round it up to seconds?
For example:
EDIT_Clip_01
00:00:03:00 + 00:00:02:19 = 00:00:05:19 ≐ 00:00:06:00
Does it make sense?
Many thanks in advance for your help.
Best,
T.
Name Track Timecode In Timecode Out Duration EDIT_Clip_01 V1 00:46:33:03 00:46:36:03 00:00:03:00 EDIT_Clip_02 V1 00:46:36:03 00:46:40:05 00:00:04:02 EDIT_Clip_03 V1 00:01:48:04 00:01:52:23 00:00:04:19 EDIT_Clip_04 V1 00:07:29:16 00:07:32:24 00:00:03:08 EDIT_Clip_05 V1 00:08:04:02 00:08:07:12 00:00:03:10 EDIT_Clip_06 V1 00:01:32:10 00:01:33:24 00:00:01:14 EDIT_Clip_01 V1 00:46:54:14 00:46:57:08 00:00:02:19 EDIT_Clip_02 V1 00:22:27:13 00:22:31:00 00:00:03:12 EDIT_Clip_03 V1 00:00:55:12 00:00:56:16 00:00:01:04 EDIT_Clip_04 V1 00:48:07:08 00:48:11:07 00:00:03:24 EDIT_Clip_05 V1 00:48:03:20 00:48:07:08 00:00:03:13 EDIT_Clip_06 V1 00:48:22:11 00:48:26:00 00:00:03:14 - Hello! I'm loving this because I've been dying to find a solution for the same problem. I tried using your formula, but I don't have the right format for the cells with my start and end times. It's reading as text and cannot calculate as a result. How would I format the input cells A2 and B2 in this example?
Thank you!!If in A2 and B2 is something like this
values are always texts. One in A2 means 2 min 8 sec and 19/24 sec. Excel doesn't support native formats for them, we need to transform by formulae. If you are on Excel 365 or 2021 duration in C2 could be calculated as
=LET( st, A2, en, B2, frames, $A$1, k, 1/60/60/24, start, TEXTBEFORE(st,":",3)+TEXTAFTER(st,":",3)/frames*k, end, TEXTBEFORE(en,":",3)+TEXTAFTER(en,":",3)/frames*k, duration, end - start, TEXT(INT(duration/k)*k,"hh:mm:ss") & ":" & ROUND(MOD(duration/k,1)*frames, 0) )and result is also returned as text.
