IBM

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.

What makes this a bit complex is that your fiscal year does not match the calendar year. If they matched, anyone hired prior to this year would not need to have their salary prorated. However, we not only need to check what year they were hired in, but in what month. e.g December 2007 gets prorated (twice - once for the first half of the month and once for the second half) but October 2007 and earlier, doesn't. So we have to check year, month and day of the month.

Now, obviously I don't know what columns you are using for what, so here are my assumptions:

Column A contains the Start Date

Column B contains the End Date (Oct 31, 2008)

Column C Contains the Annual SalaryAssuming your first Start Date is in A1, put this in D1:

=IF(YEAR(A1)<YEAR(TODAY())-1,C1,IF(YEAR(A1)<YEAR(TODAY()),IF(MONTH(A1)<11,C1,IF(DAY(A1)<15,(C1/12*(MONTH(B1)-MONTH(A1)+13)),(C1/12*(MONTH(B1)-MONTH(A1)+12)))),IF(DAY(A1)<15,(C1/12*(MONTH(B1)-MONTH(A1)+1)),(C1/12*(MONTH(B1)-MONTH(A1))))))

This should return:

- 5 months of salary from someone who started June 1 - 14, in the current year.

- 4 months of salary for someone who start June 15 - 30, in the current year.

- 12 months of salary for someone who started prior to November 1st of the prior year.

- A month's salary for someone who started prior to October 15th of the current year.

- etc.Test it out and let me know what you think...

That's very impressive DerbyDad, and much appreciated. However, I come up with a value of 0 in Column D.

Does it matter what format the dates are in Column's A and B?

The format of the date should not matter as long as it is a true date, with a month, a day and a year. I put 6/6/08 in A1

I put 10/31/08 in B1

I put 50,000 in C1

I copied the formula directly from my post and pasted it into D1It returned 20,833.33 which is 5 months salary - 50,000/12*5

I put 6/16/08 in A1

It returned 16,666.67 which is 4 months salary.

I put 11/16/07 in A1

It returned 45,833.33 which is 11 months salary.

I put 6/6/07 in A1

It returned 50,000 since the start date is prior to this fiscal year.

I can't say why it's not working for you.

It's working a treat now DerbyDad! Thanks a lot.

Mulli

Glad to have been of assitance. Have you calculated my bonus yet?

Ask Your Question

Weekly Poll

Do you think cellular roaming fees are unfair?

Discuss in The Lounge

Poll History