Film Frame Calculations

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

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

@Sergei Baklan 

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.

@rc2020WHSN 

to substract A1 from A2 with number of frames in sec in B1

image.png

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

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

@Sergei Baklan 

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

 

@Calibimist 

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

 

@facruz@Sergei Baklan 

Hello, 

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: 

EDIT_Clip_01

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.

 

Best,

T. 

 

NameTrackTimecode InTimecode OutDuration
EDIT_Clip_01V100:46:33:0300:46:36:0300:00:03:00
EDIT_Clip_02V100:46:36:0300:46:40:0500:00:04:02
EDIT_Clip_03V100:01:48:0400:01:52:2300:00:04:19
EDIT_Clip_04V100:07:29:1600:07:32:2400:00:03:08
EDIT_Clip_05V100:08:04:0200:08:07:1200:00:03:10
EDIT_Clip_06V100:01:32:1000:01:33:2400:00:01:14
EDIT_Clip_01V100:46:54:1400:46:57:0800:00:02:19
EDIT_Clip_02V100:22:27:1300:22:31:0000:00:03:12
EDIT_Clip_03V100:00:55:1200:00:56:1600:00:01:04
EDIT_Clip_04V100:48:07:0800:48:11:0700:00:03:24
EDIT_Clip_05V100:48:03:2000:48:07:0800:00:03:13
EDIT_Clip_06V100:48:22:1100:48:26:0000:00:03:14

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