Prorated Salary Formula

I need to develop a formula which will determine an employee's earnings for the fiscal year, November 1 - October 31, based on a prorated salary, in order to determine a bonus amount.

Example: Joe's salary is $50,000/yr and he started June 7, 2008. He would be entitled to a bonus based on prorated earnings for June to October 31(because he worked more than half of June)

However, Carol's salary is also $50,000/yr but she started after June 16. She would be entitled to a bonus based on prorated earnings for July to October 31 (because she worked less than half of June).

What formula would I use? How should the dates appear in the 'Start Date' Column?

We also have employees who started in previous fiscal years, how do I exclude them from the prorated calculation.

Thank you for your time.

Brandy

Hi Brandy
I am assuming that you are working on a monthly basis.
try the following formula:

=IF(A7<=$A$1,1,IF(DAY(A7)>=16,(MONTH($B$1)-MONTH(A7)-1)/12,(MONTH($B$1)-MONTH(A7))/12))

this returns 1 if the stating date is before the start of the year 1/11/07 and a decimal ,in the case of 7/6/08 .33333 and in the case of 17/7/08 it returns .25. A1 is the start of year B1 is end of year. Rest of col A is start date.
I have attached a worksheet so you can see with afew examples.

Regards Howard

Thanks Howard!

It works well up to September 15, 2008 after this date it returns a value of O.

I'd like it to return a value up to October 15, 2008 and 0 after that date.

Any ideas?

Brandy

Hi Sglife
It took forever for the penny to drop as to what I was doing wrong. I tried both my formula and yours side by side, but was convinced that my answers were correct eventually the penny dropped and once this happenedit was fairly simple to change my formula to give the same answers as yours.
Hi Brandy
I had been aware though that there was going to be a problem with the month of February the way both our formulas were set up so I entered a list of months(1 to 12) with the number of days divided by 2 and then used Vlookup to find the halfway point in the month my ammended fomula is:

=IF(A2<=$A$1,1,IF(DAY(A2)<=VLOOKUP(MONTH(A2),$L$1:$M$12,2,FALSE),(MONTH($B$1)-MONTH(A2)+1)/12,((MONTH($B$1)-MONTH(A2))/12)))

Regards Howard