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