Film Frame Calculations

%3CLINGO-SUB%20id%3D%22lingo-sub-280957%22%20slang%3D%22en-US%22%3EFilm%20Frame%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280957%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20work%20out%20how%20to%20sum%20a%20film%20frame%20calculation.%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3E17%3A40%3A45%3A07%3C%2FP%3E%3CP%3E12%3A02%3A34%3A17%3C%2FP%3E%3CP%3E05%3A54%3A28%3A19%3C%2FP%3E%3CP%3Ethese%20are%20hours%3Aminutes%3Aseconds%3Aframes%3C%2FP%3E%3CP%3E24%20frames%20makes%20a%20second%20in%20this%20scenario%20(alternatives%20are%20that%2025%20or%2030%20frames%20make%20a%20sec)%3C%2FP%3E%3CP%3Esetting%20the%20custom%20cell%20to%20hh%3Amm%3Ass%3Aff%20doesn't%20work%20and%20it%20always%20seems%20to%20revert%20to%20the%20time%20and%20when%20I%20attempt%20a%20formula%20to%20sum%20the%20numbers%20it%20never%20works.%20%26nbsp%3BAny%20help%20would%20be%20greatly%20received!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-280957%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-292776%22%20slang%3D%22en-US%22%3ERe%3A%20Film%20Frame%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-292776%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Pamela%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20present%20result%20with%20frames%20formula%20will%20be%20more%20complicated%3C%2FP%3E%0A%3CPRE%3E%3DTEXT(SUMPRODUCT(IFERROR(LEFT(D3%3AD9%2C8)*1%2C0))%2BINT(SUMPRODUCT(IFERROR(RIGHT(D3%3AD9%2C2)*1%2C0))%2F%24F%242)%2F24%2F60%2F60%2C%22%5Bhh%5D%3Amm%3Ass%22)%20%26amp%3B%20%22%3A%22%20%26amp%3B%20TEXT(INT((SUMPRODUCT(IFERROR(RIGHT(D3%3AD9%2C2)*1%2C0))%2F%24F%242-INT(SUMPRODUCT(IFERROR(RIGHT(D3%3AD9%2C2)*1%2C0))%2F%24F%242))*%24F%242)%2C%2200%22)%3C%2FPRE%3E%0A%3CP%3E(array%20formula)%3C%2FP%3E%0A%3CP%3ETo%20sum%20only%20two%20values%20that's%20regular%20formula%3C%2FP%3E%0A%3CPRE%3E%3DTEXT(LEFT(H10%2C8)%2BLEFT(H27%2C8)%2BINT((RIGHT(H10%2C2)%2BRIGHT(H27%2C2))%2F%24F%242)%2F24%2F60%2F60%2C%22%5Bhh%5D%3Amm%3Ass%22)%20%26amp%3B%20%22%3A%22%20%26amp%3B%20TEXT(INT(((RIGHT(H10%2C2)%2BRIGHT(H27%2C2))%2F%24F%242-INT((RIGHT(H10%2C2)%2BRIGHT(H27%2C2))%2F%24F%242))*%24F%242)%2C%2200%22)%3C%2FPRE%3E%0A%3CP%3EIn%20attached%20file%20there%20is%20a%20small%20difference%20with%20calculator%20you%20mentioned.%20In%20calculator%20if%20enter%20the%20time%20with%20frames%20like%2020%3A42%3A16%3A10%20it%20shows%2020%3A42%3A16%3A11%2C%20i.e.%20adds%20one%20frame%20to%20each%20entered%20value.%20Don't%20know%20is%20that%20by%20design%20or%20bug.%3C%2FP%3E%0A%3CP%3EIn%20the%20attached%20file%20calculated%20results%20are%20on%20the%20right%20from%20your%20ones.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-292540%22%20slang%3D%22en-US%22%3ERe%3A%20Film%20Frame%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-292540%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3EThanks%20for%20that%20formula%26nbsp%3B-%20I've%20changed%20the%20sheet%20a%20bit%20and%20its%20not%20quite%20working%20so%20I'm%20wondering%20if%20you%20could%20have%20a%20look%20at%20this%20version%3F%20%26nbsp%3BAlso%20I'm%20trying%20to%20point%20some%20of%20the%20figures%20into%20the%20tabs%20prior%20including%20adding%20up%20partial%20days%20so%20I've%20added%20in%20a%20line%20to%20calculate.%20%26nbsp%3BI%20managed%20to%20add%20a%20few%20things%20manually%20through%20a%20TC%20calculator%20I%20found%20online%20(%3CA%20href%3D%22https%3A%2F%2Fcurrent360.com%2Fapps%2Ftimecode-calculator%2Ftc.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcurrent360.com%2Fapps%2Ftimecode-calculator%2Ftc.html%3C%2FA%3E)%20but%20it%20stops%20when%20it%20hits%20over%20100%20hours.%20Wasn't%20quite%20matching%20up.%20%26nbsp%3BAnything%20your%20able%20to%20shed%20a%20light%20on%20would%20be%20helpful.%20%26nbsp%3BThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281446%22%20slang%3D%22en-US%22%3ERe%3A%20Film%20Frame%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281446%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Pamela%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20be%20array%20formula%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(IFERROR(LEFT(D2%3AD22%2C8)*1%2C0))%2BSUMPRODUCT(IFERROR(RIGHT(D2%3AD22%2C2)*1%2C0))*%24F%242%2F24%2F60%2F60%3C%2FPRE%3E%0A%3CP%3Ewhere%20number%20of%20frames%20is%20in%20F2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1831387%22%20slang%3D%22en-US%22%3ERe%3A%20Film%20Frame%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1831387%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3Ethe%20formula%20is%20extremely%20helpful%20for%20additions.%20What%20do%20I%20have%20to%20change%20in%20the%20formula%20for%20subtractions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E01%3A20%3A14%3A22%20minus%2000%3A00%3A10%3A02%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20but%20could%20not%20find%20a%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 (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!

5 Replies
Highlighted

Hi Pamela,

 

Could be array formula

=SUMPRODUCT(IFERROR(LEFT(D2:D22,8)*1,0))+SUMPRODUCT(IFERROR(RIGHT(D2:D22,2)*1,0))*$F$2/24/60/60

where number of frames is in F2

 

Highlighted

Hi Sergei,

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.

Highlighted

Hi Pamela,

 

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

(array formula)

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.

Highlighted

@Sergei Baklan 

Hi Sergei,

the formula is extremely helpful for additions. What do I have to change in the formula for subtractions?

 

Example:

01:20:14:22 minus 00:00:10:02 

 

I tried but could not find a solution.

Highlighted

@rc2020WHSN 

to substract A1 from A2 with number of frames in sec in B1

image.png

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