timecode cell formula

**russpond** · 11-19-2008, 10:30 AM

I'm working on a feature film, and part of my deliverables is a spotting list. This is where I have to go through the entire movie and document each line of dialogue. Each line of dialogue has a start timecode and a stop timecode, and a duration. I need some help building a cell formula that will find the difference in the start and stop timecodes.

Here's the timecode format HH:MM:SS:FF

HH: Hours
MM: Minutes
SS: Seconds
FF: Frames (24 fps)

For example, this is a line from my document:

01:58:04:10, 01:58:05:11, 1:01, "It should have been contained."
(start timecode), (stop timecode), (duration), (line of dialogue)

The challenge has been this issue of frames, and dealing with the base of 24.

Does anyone have any ideas on how I can create a formula that calculate the difference? I need each timecode entry to stay within one cell.

Any help would be great appreciated!

Thanks!
Russ

**oldchippy** · 11-19-2008, 11:30 AM

Hi,

Does this previous post help?

http://www.excelforum.com/excel-prog...-timecode.html

**MickG** · 11-19-2008, 12:27 PM

Hi, Try this UDF:-
To insert function, Right click Sheet Tab, Select "View Code", From toobar select "Insert", " Module", New Windopw appears,Paste entire code in VB Window.
To Run enter Function in cell for Time Duration, as:- FF(stop timecode, start timecode)
NB:- The Start /Stop timecodes includes your FF bit

Please Login or Register  to view this content.

Regards Mick

**russpond** · 11-20-2008, 10:05 AM

Thanks Mick.

I tried using your formula, but I keep getting a "Compile Error: Sub or Function not defined". It highlights the InStrRev function.

Any ideas on what is causing that?

Russ

**MickG** · 11-20-2008, 11:35 AM

Hi, Have a look at the attached, see if it helps !.
Regards Mick

**russpond** · 11-20-2008, 11:44 AM

Yeah, same error when I open your sheet. Attached is a screenshot.

It's probably a version thing. I'm using Excel v.X for Mac. I wouldn't think it would be a problem, though.

Russ

**MickG** · 11-20-2008, 01:14 PM

Hi,I think the Mac is the Problem, I try and think of an alternative.
Mick

**MickG** · 11-20-2008, 01:42 PM

Hi, If we assume the Time has 8 digits then this would work.
Seems logical

Please Login or Register  to view this content.

Regards Mick

**russpond** · 11-20-2008, 04:24 PM

Mick,

If I grab the left 8 of 01:58:04:10, then I only get 01:58:04 (with no frames). So, it won't calculate the correct difference.

For example, if my start timecode is 01:58:04:10 and my ending timecode is 01:58:04:23, the result should be 00:13.

Or, another example would be:
st = 01:58:04:23
Ed = 01:58:05:15
FFA should be 00:16

Or, am I missing something?

Russ

**MickG** · 11-22-2008, 09:16 AM

Hi, Russ
Try this:- It should now give you the total time Difference includiing the FF Bits to base 24.

Please Login or Register  to view this content.

Regards Mick

**russpond** · 11-22-2008, 09:27 AM

Hey Mick,

I just ordered the latest version of MS Office (Mac version 2008). I should be here Monday. Once I get it installed, I'll try out the new function and let you know.

Thanks for all your help.
Russ

**shg** · 11-22-2008, 02:01 PM

You could use this in lieu of InStrRev:

Please Login or Register  to view this content.

**daddylonglegs** · 11-22-2008, 04:37 PM

Here's a formula approach......

If start timecode is in A2 and end timecode in B2 you can use this formula in C2

=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/86400,"m:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),24),"\:00")

**russpond** · 11-22-2008, 07:08 PM

DaddyLongLegs,

That was perfect! It works like a charm, and there are no macros.

I changed the output format slightly. I like the result to look like s:ff, or 4:03, so I changed it as such:

=TEXT(LEFT(E11,8)-LEFT(D11,8)-(RIGHT(D11,2)>RIGHT(E11,2))/86400,"s")&TEXT(MOD(RIGHT(E11,2)-RIGHT(D11,2),24),"\:00")

Awesome!

Thanks,
Russ

timecode cell formula

LinkBack

Thread Tools

Display

timecode cell formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions