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

 

ericmcasalini's avatar
ericmcasalini
Copper Contributor
May 14, 2024
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!!
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 14, 2024

    ericmcasalini 

    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.

Resources