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!
Last edited by donkey punch; 06-01-2010 at 06:13 AM. Reason: Fixing Title...
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.
Do you mean 2 minutes or 2 hours ?
Presumably frames are always listed irrespective of other values ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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)
Last edited by DonkeyOte; 06-01-2010 at 07:10 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 :-)
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!
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
works sweet thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks