 • 379K Members
• 2,595 Online
• 392K Conversations

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

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

Related Conversations