Complex Calculation HELP!

[BronwynAnne]

Okay, I've been trying to figure this out for a few years, but I am finally breaking down and asking for help.

I work in motion picture film editorial and one of my many duties is to add and subtract film footages. Not so hard doing so in your head, or w/ a Film Calculator, but kinda difficult to create a calculation to do it for you.

The rules to adding and subtracting film are as follows:

There are 16 frames in a foot of 35 mm motion picture film with the first frame being +00 and the sixteenth frame being +15 (yes, I know that's confusing you count the 00 as 1). Also, when adding footage, subtract 1 from the total, and when subtracting footage add 1 to the total.

Film has a number printed on the edge every foot called a Key Number or KN for short.

It resembles this: KI 43 2579-0274+00

The KI 43 2574 does not change and is called a Prefix.

The 0274 is the Footage and advances one per 16 frames.

and +00 is the frame counter

The footage would advance like so:

KI 43 2579-0274+00

+01

+02

+03 ... +14

+15

KI 43 2579-0275+00

I have broken the key number into 3 fields: Prefix, footage and frame. I have figured out how to subtract or add two footages by converting them to frames by using this calculation:

(KN Out Foot*16)+(KN Out Frame) - (KN In Foot*16)-(KN In Frame)+1

The problem is that I can not figure out how to reverse the process and convert frames to feet and frames.

Any suggestions?

Thanks!

Bronwyn

[djgogi]

KN=Prefix & "-" & right("0000"&Int(FrameCount/16),4) & "+" & right("00"&Mod(FrameCount,16),2)

Dj

[BronwynAnne]

I gave that a try and it comes up as zero. The field Total Scan Length is a calculation resulting in a number. The fields it references are also numbers. I have the result of your suggested formula set to be text. Would these factors have any effects on the result?

Total Scan Length=Key Prefix & "-" & Right("0000" & Int(Total Scan Length/16),4) & "+" & Right("00"&Mod(Total Scan Length,16),2)

Although I think I can see where you may be going with this calculation. Maybe I can tinker it.

Thanks!

Bronwyn

[Damocles]

Did you ever get a solution for this? DO you still care if you find one?