TRUNC formula

**donkey punch** · 06-01-2010, 05:58 AM

I've run into a problem with a formula I need to subtract time code from video tape. An example of the time code looks like this: 2.21.03

2 = hours (made up of 60 seconds per hour)
21 = seconds (made up of 24 frames per second - film speed)
03 = frames

I've been able to truncate and then subtract the following: 21.03 less 22.19 = 1.16 (or 1 sec 16 frame difference).

I've attached an Excel sheet example to show you.

What I'm after is expanding this formula to include hours in the equation.

Help!

**daddylonglegs** · 06-01-2010, 06:02 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**DonkeyOte** · 06-01-2010, 06:57 AM

Do you mean 2 minutes or 2 hours ?

Presumably frames are always listed irrespective of other values ?

**DonkeyOte** · 06-01-2010, 07:04 AM

I'm heading off line - assuming the above is indeed the case then one route might be:

B2:
=SUM(FLOOR(SUBSTITUTE(REPT("0.",2-(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))&A2,".",":",1),"0:0:1")*86400,RIGHT(A2,2)/24)
copied to other cells

that should account for values either with/without "hour" (minute?) value

Note the above displays seconds in decimal form - if you want the result as a true time value then divide the result by 86400 and format cell as: [ss].000

edit: FWIW you might also want to check out the DOLLARDE/FR functions - these form part of the Analysis ToolPak Add-In (requires activation pre XL2007 via Tools)

**donkey punch** · 06-01-2010, 08:02 AM

Hey thanks I think that's it! Sorry I didn't get back before I was called away. Yes, I did mean minutes (not hours) sorry about that! Thank you so much :-)

**donkey punch** · 06-01-2010, 08:47 AM

Dear DonkeyOte,

The formulas are working great except the end result is still showing in decimal instead of frames eg: E2 is showing 60.08 (8/100) instead of 60.02 (2/24).

I've attached an updated Excel sheet with your formulas added.

Thanks!

**DonkeyOte** · 06-01-2010, 10:27 AM

If you wish to store frames I would not suggest storing as a Time value given the base 24 instead of ms .. could otherwise lead to confusion.

On that basis - perhaps:

E2: =ROUND(INT(F2)+MOD(F2,1)*0.24,2)
copied down

format as Number to 2 decimals