Forum Discussion

Pamela Harvey-White's avatar
Pamela Harvey-White
Copper Contributor
Nov 01, 2018

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 (a...
excel
Formulas and Functions
facruz's avatar
facruz
Copper Contributor
Jan 31, 2024

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

 

Tess33's avatar
Tess33
Copper Contributor
Feb 13, 2024

facruz, SergeiBaklan 

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
  • facruz's avatar
    facruz
    Copper Contributor
    Feb 14, 2024

    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?

    • Tess33's avatar
      Tess33
      Copper Contributor
      Feb 14, 2024

      facruz In a separate column please. 

      • facruz's avatar
        facruz
        Copper Contributor
        Feb 16, 2024
        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.

Resources