Film Frame Calculations

Copper Contributor

I am trying to work out how to sum a film frame calculation.

For example:




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!

16 Replies

Hi Pamela,


Could be array formula


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 ( 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?



01:20:14:22 minus 00:00:10:02 


I tried but could not find a solution.


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

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


I think I found a solution for B2 minus A2, 24 frames per second.




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

@Pamela Harvey-White  A friend had the same issue and asked for my help to calculate the duration between two cells in hh:mm:ss:ff (ff being the frames) format. I've created a formula that does the trick with precise results. It first subtracts hh:mm:ss and then adds the frames. If the subtraction between frames is less than 0, it subtracts a second and then adds the frames. Then it trims the text to ensure negative values aren't preceded by a minus sign.

This thing is not allowing me to attach files, so I'll just paste the formula here. In a table which has the starting time in cell A2 and the ending time in cell B2, it would go like this, with the duration in C2:
A2: 00:02:08:19
B2: 00:02:37:18
C2: =IF((RIGHT(B2,2)-RIGHT(A2,2))<0,TEXT(LEFT(B2,8)-LEFT(A2,8)-1/86400,"HH:MM:ss")&":"&RIGHT(TEXT(24+RIGHT(B2,2)-RIGHT(A2,2),"00"),2),TEXT(LEFT(B2,8)-LEFT(A2,8),"HH:MM:ss")&":"&RIGHT(TEXT(RIGHT(B2,2)-RIGHT(A2,2),"00"),2))

The result in C2 should be 00:00:28:23




I would like to thank you for the formulas you've created. They saved me a lot of my time. 

I was wondering whether there is any formula which can find clip based on the name, count both durations and round it up to seconds? 


For example: 


00:00:03:00 + 00:00:02:19 = 00:00:05:19  00:00:06:00


Does it make sense? 

Many thanks in advance for your help.





NameTrackTimecode InTimecode OutDuration

@Tess33 I'm glad the formula was useful! The formula would be applied in a separate column or you'd need it nested in the same formula which I shared calculating the duration?

@facruz In a separate column please. 

You'd need to put this formula in D2 for the rounded duration: =IF(NUMBERVALUE(RIGHT(C2,2))<12,LEFT(C2,8),LEFT(C2,8)+1/86400)

11 frames or less will round downwards and 12 frames or more will round upwards.
Thanks @facruz. It works well for rounding up the duration. However I was looking for the formula which can find clips with the same name, count durations of these clips and then round the total duration up to seconds.

For example:
EDIT_Clip_01 (row 2 and 8)
00:00:03:00 + 00:00:02:19 = 00:00:05:19 ≐ 00:00:06:00

Is it possible or is that something that excel can't do at all?

Thank you
Hello! I'm loving this because I've been dying to find a solution for the same problem. I tried using your formula, but I don't have the right format for the cells with my start and end times. It's reading as text and cannot calculate as a result. How would I format the input cells A2 and B2 in this example?

Thank you!!


If in A2 and B2 is something like this


values are always texts. One in A2 means 2 min 8 sec and 19/24 sec. Excel doesn't support native formats for them, we need to transform by formulae. If you are on Excel 365 or 2021 duration in C2 could be calculated as


=LET( st, A2,
      en, B2,
      frames, $A$1,
      k, 1/60/60/24,
      start, TEXTBEFORE(st,":",3)+TEXTAFTER(st,":",3)/frames*k,
      end, TEXTBEFORE(en,":",3)+TEXTAFTER(en,":",3)/frames*k,
      duration, end - start,
      TEXT(INT(duration/k)*k,"hh:mm:ss") & ":" & ROUND(MOD(duration/k,1)*frames, 0)


and result is also returned as text.