C

#### Cliff Partridge

some trouble.

A B C D E F

1 Revenue

2 Compensation Hire Date 2005 2006 2007 2008

3 $100K 06/01/2005 $8,333 $158,333 $258,333 $300,000

4

Revenue Variables:

First 6 months = 0% of Comp

Second 6 months = 100% of Comp

Second 12 months = 200% of Comp

Third 12 months + = 300% of Comp

I want formulas in cells C3:F3 to calculate the revenue based on hire

date and revenue variables.

(I entered the correct revenue numbers for the example in row 4 to

help with formula creation)

As you can see in row 3, the 2005 calculation will have 6 months of no

revenue and 1 month of revenue at 100% of the person's total

compensation. 2006 will have 5 months at 100%, and 7 months at 200%

of comp. Then 2007 will be 5 months at 200% of comp, and 7 months at

300% of comp. Finally 2008 will be at 300% of compensation.

I would like the formulas to work no matter what Hire Date is entered

in year 2005-2008.

I've tried several variables of DATEDIF and IF formulas to no avail.

I get stuck when I have to pick up a partial period like the example

above (calculating how much of a variable period was calculated

last year etc.); if everything was a neat 6 months all the time, it

would be pretty easy.

Thanks for your help.

cp