Time Code Calculator 30fps - Mac

[sunrhat]

Hi,

I found this great formula posted on this site for calculating time code @ 24fps:

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

Can anyone tell me how to change it for 30fps? I'm using Excel 2008 for Mac, so I'd rather use a formula than figure how to make VBA work on Macs.

Thanks,
k8

[shg]

You might also post the inputs to the formula, huh?

[sunrhat]

Does this help?

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

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

Example:

01:58:04:10, 01:58:05:11, 1:01
(start timecode), (stop timecode), (duration)

Thanks

[shg]

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

This doesn't accommodate drop-frame timing for NTSC, but should be correct for HD.

[sunrhat]

Thank you for your fast response!

[shg]

You're welcome. Would you please mark the thread as Solved?

[sunrhat]

Yes, just looking for how to do that...

[shg]

How To ... in the menu bar.

Also in the forum rules ...

[austinmjames]

See Attached.

[rehamelin]

Is there anyway we can format the cells so we don't have to type the ":" for each TC?

I'm not very good with excel and I need a spreadsheet just like yours to calculate timecodes. I works great for calculation, but having some kind of "auto-formating" for the timecodes we input in the first two columns, so that we could type "10011500" instead of "10:01:15:00" would help a lot.

Thanks for your help!...

[austinmjames]

Ya, you can right click on the cells you want to auto-format > format cells > custom > input= 00":"00":"00":"00 > done.

[rehamelin]

Ya, you can right click on the cells you want to auto-format > format cells > custom > input= 00":"00":"00":"00 > done.

I works for the format of the value typed, the colons are being added automatically, but it seems to be messing with the function for duration. I tried it, and the duration column displays a value error. Any idea to solve this, so that when I type in the TCin/TCout it gets the right formatting and the duration would calculate correctly??

[giglanski]

Thanks Thanks Thansk

[howiechin]

Hi Austin,

Just wanna cross check with you regarding the attached EDL TC Calculator.xls

It's a post that is a few years old but i found a bug in the excel sheet. When ur TC-out is only 00:00:11:00 - the total reports it as 00:00:15:00. This is baffling and weird. did U know about this?