# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] 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!...  :Wink:

----------


## 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?

----------

