# Excel Prorated Salary Formula

IBM
September 24, 2008 at 10:12:04
Specs: Windows XP, P3 384MB RAM

 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.

See More: Excel Prorated Salary Formula

#1
September 24, 2008 at 11:50:05

 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 DateColumn 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:=IF(YEAR(A1)

Report •

#2
September 24, 2008 at 13:55:12

 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?

Report •

#3
September 24, 2008 at 15:14:18

 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 A1I put 10/31/08 in B1I put 50,000 in C1I copied the formula directly from my post and pasted it into D1It returned 20,833.33 which is 5 months salary - 50,000/12*5I put 6/16/08 in A1It returned 16,666.67 which is 4 months salary.I put 11/16/07 in A1It returned 45,833.33 which is 11 months salary.I put 6/6/07 in A1It returned 50,000 since the start date is prior to this fiscal year.I can't say why it's not working for you.

Report •

Related Solutions

#4
September 25, 2008 at 10:30:13

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

Report •

#5
September 25, 2008 at 11:36:36

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

Report •