CODEWITHSUNDEEP
excelexcel-formulaworksheet-functionworksheet
Faizal Nor
Faizal Nor

Reputation: 13

How to calculate workdays and compensation for given period based on Employee's monthly salary?

I have only three editable fields:

  1. salary = 15,000
  2. start date = 18 Jul 2014
  3. end date = 12 Oct 2014

With these fields, I need to calculate the total salary I need to pay. Payment is on a monthly basis:

(JULY = 15,000 / 31 * 14) +
(AUG = 15,000 / 31 * 31) +
(SEPT = 15,000 / 30 * 30) +
(OCT = 15,000 / 30 * 12).

I can get total days based on both dates (ie 87 days, =DAYS($enddate,$startdate)+1) ) but I need to split the days according to the months.

What formula do I need automatically to get the amount, because each person will have different salary and different dates?

Upvotes: 0

Views: 2554

Answers (2)

pnuts
pnuts

Reputation: 59485

I have split this out because I am not entirely sure what you require and you may be able to assemble the pieces in a way that better suits you:

Assuming your data is in A1:A3, put 1/7/14 in C1, 1/8/14 in D1, 1/9/14 in E1 and 1/10/14 in F1.

To count the number of applicable days by month, in C2 enter: 

=IF(MONTH($A2)=MONTH(C1),EOMONTH(C1,0)-$A2+1,IF(MONTH($A3)=MONTH(C1),DAY($A3),EOMONTH(C1,0)-EOMONTH(C1,-1)))

To compute the salary for the month by computing the daily rate for the applicable month and multiplying that by the number of days from above, in C3 enter: 

=$A1*C2/(DAYS(EOMONTH(C1,0),C1)+1)

Format C2:C3 to suit and copy across to F2:F3.

Upvotes: 0

Alexander Bell
Alexander Bell

Reputation: 7918

You can use Excel VBA custom Function to solve your problem:

1). First, you should populate Excel Worksheet with data structure reflecting Employees Name, Monthly Salary, StartDate and EndDate, like in the following sample:

Employee    M.Salary    Start Date  EndDate
John     $15,000.00     7/18/2014   10/12/2014
Ann      $20,000.00     7/19/2014   10/13/2014
Peter    $16,000.00     7/20/2014   10/14/2014
Jeff     $25,000.00     7/21/2014   10/15/2014

2). The DAYS in date range can be simply found by subtraction (EndDate-StartDate), because the underlying data type in integer

3). For general solution to the problem (Calculate the compensation for any arbitrary period and monthly salary) you will need to create custom VBA formula and use it in a separate column for each Employee. Refer to this article for explanation: Create a custom worksheet function in Excel VBA

4). Pertinent to your particular case with fixed date rage, the simplified solution based on the Excel Worksheet formulas (no VBA) is described below:

Employee MoSalary       Start       End         Days FullMo     FirstMo        LastMo        Total
John     $15,000.00     7/18/2014   10/12/2014  87  30000.00     $6,774.19   $6,000.00   $42,774.19 
Ann      $20,000.00     7/18/2014   10/12/2014  87  40000.00     $9,032.26   $8,000.00   $57,032.26 
Peter    $16,000.00     7/18/2014   10/12/2014  87  32000.00     $7,225.81   $6,400.00   $45,625.81 
Jeff     $25,000.00     7/18/2014   10/12/2014  87  50000.00     $11,290.32      $10,000.00      $71,290.32

4a). In column E starting with row 2 add formula for DAYS : =(D2-C2)+1 and extend it for entire range

4b). In column F starting with row 2 add formula for whole months : =2*B2 and extend it for entire range

4c). In column G starting with row 2 add formula for the first month : =14*B2/31 and extend it for entire range

4d). In column H starting with row 2 add formula for last month : =12*B2/30 and extend it for entire range

4e). In column I starting with row 2 add formula for total compensation : =SUM(F2:H2) and extend it for entire range

Hope this will help. Best regards,

Upvotes: 0

Related Questions