Frame Calculator
Posted by Dave on January 07, 2002 7:48 AM
Hi,
I'm trying to come up with some formatting and a formula that will allow me to subtract or add film frames to eachother. This should display HH:MM:SS:FF where FF is frames and there are 25 frames to a second. People should be able to just enter an 8 figure number (eg: 01234512) which should then display 01:23:45:12 and subtracting 01:23:45:11 from this should give you 00:00:00:01 whilst subtracting 01:23:44:11 should give you 00:00:01:01
I hope that's clear. Any suggestions would be gratefully received.
Dave
Posted by Dan on January 07, 2002 11:41 AM
Your question is difficult for a number of reasons. (Notice that no one has yet responded!) This is the closest that I can come up with right now:
I think your best bet is to enter the number in the manner that you want to, let's say in column A. Format column A in a custom format of: 00000000 so that the leading zero will show if necessary.
In column B, say cell B1, write the (lengthy) formula:
=IF(ISNUMBER(B1),SUM((RIGHT(ROUNDDOWN(B1,-6)/1000000,2))/24,(RIGHT(ROUNDDOWN(B1,-4)/10000,2))/1440,(RIGHT(ROUNDDOWN(B1,-2)/100,2))/86400,RIGHT(B1,2)/100/86400),"")
And copy this formula down column B. Also format column B as: [h]:mm:ss.00
This will put your number in format "hh:mm:ss.ff". The frames are actually showing up as a decimal of a second. You should then be able to add and subtract normally. One note, however, is that this does not take in to account that there are 25 frames in a second. If you add two frames and the total is greater than 25, it will just add it up. Example 01234512 + 01234520 = "2:47:30.32". The other thing is if the second cell has a higher frame number than the first then that will affect the seconds, when subtracting. Example: 1234512 - 1234420 = "0:00:00.92". There are probably ways around it, but let me know first if that is an issue before we deal with that.
HTH.
