|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 Salary
Assuming your first Start Date is in A1, put this in D1:
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.
Test it out and let me know what you think...