Forum Discussion
Film Frame Calculations
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.
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.
