10-31-2018 08:11 PM
10-31-2018 08:11 PM
I am trying to work out how to sum a film frame calculation.
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!
11-01-2018 04:55 PM
Could be array formula
where number of frames is in F2
11-28-2018 09:07 PM
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.
11-29-2018 07:33 AM
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")
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.
10-29-2020 02:02 PM
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")