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
@Sergei BaklanThank 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")