 # 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!

8 Replies

# Re: 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

# Re: Film Frame Calculations

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.

# Re: Film Frame Calculations

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.

# Re: Film Frame Calculations

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.

# Re: Film Frame Calculations

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")``

# Re: Film Frame Calculations

@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.

# Re: Film Frame Calculations

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")``

# Re: Film Frame Calculations

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")``