Forum Discussion
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...
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!!
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.