Adding and Subtracting Timecode in Excel

Question

I need to add and subract timecode in excel. My format for timecode is hh:mm:ss;ff or hours:minutes:seconds;frames.

So an example of subtraction would be 01:01:07;27 - 00:00:06;00 = 01:01:01;27

I need to know how to enter the subtraction formula in Excel.

Answer 1

use this formula:

=TEXT(INT(((LEFT(A1,FIND(";",A1)-1)+(MID(A1,FIND(";",A1)+1,3)/(29.97*24*60*60)))-(LEFT(B1,FIND(";",B1)-1)+(MID(B1,FIND(";",B1)+1,3)/(29.97*24*60*60))))*24*60*60)/(24*60*60),"hh:mm:ss") & ";" & TEXT(MOD(((LEFT(A1,FIND(";",A1)-1)+(MID(A1,FIND(";",A1)+1,3)/(29.97*24*60*60)))-(LEFT(B1,FIND(";",B1)-1)+(MID(B1,FIND(";",B1)+1,3)/(29.97*24*60*60))))*24*60*60,1)*29.97,"00.00")