Oct 31 2018 08:11 PM
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!
Nov 01 2018 04:55 PM
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
Nov 28 2018 09:07 PM
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.
Nov 29 2018 07:33 AM
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.
Oct 29 2020 01:54 AM
Hi Sergei,
the formula is extremely helpful for additions. What do I have to change in the formula for subtractions?
Example:
01:20:14:22 minus 00:00:10:02
I tried but could not find a solution.
Oct 29 2020 02:02 PM
to substract A1 from A2 with number of frames in sec in B1
perhaps formula in A4 could be
=TEXT(FLOOR.MATH(LEFT(A2,8)-LEFT(A1,8)+(RIGHT(A2,2)-RIGHT(A1,2))/$B$1/24/60/60,"00:00:01"),"hh:mm:ss") & "."&TEXT(MOD(RIGHT(A2,2)-RIGHT(A1,2),$B$1),"00")
Dec 16 2020 03:38 PM - edited Dec 16 2020 05:42 PM
@SergeiBaklanThank you for your formulas, there are very helpful. Except there is a bug for one instance. When I calculate E2, 1:00:13:00, minus D2, 1:00:01:00, using your formula:
=TEXT(FLOOR.MATH(LEFT(E2,8)-LEFT(D2,8)+(RIGHT(E2,2)-RIGHT(D2,2))/24/24/60/60,"00:00:01"),"hh:mm:ss") & ":"&TEXT(MOD(RIGHT(E2,2)-RIGHT(D2,2),24),"00")
The result gives 0:00:11:00, instead of 0:00:12:00. Would you be able to take a look at it? Thank you very much.
Dec 16 2020 05:39 PM
I think I found a solution for B2 minus A2, 24 frames per second.
=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/(24*60*60),"hh:mm:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),24),"\:00")
Dec 17 2020 02:43 AM
That's rounding error, I'd add some small fraction
=TEXT(FLOOR.MATH(LEFT(D2,8)-LEFT(D1,8)+(RIGHT(D2,2)-RIGHT(D1,2)+0.00001)/$B$1/24/60/60,"00:00:01"),"hh:mm:ss") & "."&TEXT(MOD(RIGHT(D2,2)-RIGHT(D1,2),$B$1),"00")
Jan 31 2024 12:36 AM - edited Feb 16 2024 01:33 PM
@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:23
Feb 13 2024 09:43 AM
Hello,
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 |
Feb 14 2024 03:54 AM
@Tess33 I'm glad the formula was useful! The formula would be applied in a separate column or you'd need it nested in the same formula which I shared calculating the duration?
Feb 16 2024 01:52 PM
Feb 18 2024 02:51 AM
May 14 2024 10:25 AM
May 14 2024 12:47 PM - edited May 14 2024 12:49 PM
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.