Home

Film Frame Calculations

Pamela Harvey-White
New Contributor

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!

3 Replies

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.