Calculating Salary based on Date Range

[Guest]

I have a need to calculate salary for an individual based on a date range.
The logistics would work like this:

Start Date End Date Salary
01/01/2007 06/17/2007 50,000
06/17/2007 08/04/2007 55,000
08/05/2007 12/31/2007 65,000

For full months from 01/01/2007 to 06/17/2007, I simply need to divide the
salary amount by 12 to get the monthly amount (in this case $4,166.67),
multiply that monthly amount by the number of full months in the range (5 *
4,166.67) $20,833.35, then look at the number of business days in the entire
month of June (which is 21), count the number of business days from the
beginning of June up to and including the 17th (which is 11), take the
monthly salary amount from above of $4,166.67 and convert it do a daily rate
for June ($4,166.67/21=$198.41) and multiply it by the number of actual
business days in June that the person was at that salary
($198.41*11=$2,182.51). Then I add the partial month (June)'s salary of
$2,182.51 to the $20,833.35 for the full months from January up to June to
get the person's salary for that date range, in this case $23,015.86.

I need to do this for each pay period. I had devised a way to calculate the
salary based on a range where the first day of the range was the first of the
month using networkdays and eodate. However, I'm thrown when the date range
starts in the month rather than the 1st. In addition, I'm especially thrown
when neither the start date or end date are at the beginning or end of their
respective months.

Any guidance would be appreciated. I tried to simplify this by calculating
the number of networkdays in the year and finding the proportion of
networkdays in the date range but that convention is not flying with my
superiors. They want the full months counted as 1/12 of annual salary but
partial months calculated based on a proportion of working days.

Thanks in advance for any help or direction.

 

[Don Guillett]

If you want a breakdown so you can explain to the employee
H I J K L M N O P
1-Feb 12-Feb 100000 8333.333 20 8 $ 8,333.33 $3,333.33
$11,666.67
1-Jan 17-Jun 50,000 4166.667 21 11 $20,833.33 $2,182.54
$23,015.87
17-Jun 4-Aug 55,000 4583.333 23 3 $32,083.33 $ 597.83
$32,681.16
5-Aug 31-Dec 65,000 5416.667 21 21 $59,583.33 $5,416.67
$65,000.00

h-J your example
k=J2/12
L=NETWORKDAYS(DATE(YEAR(I2),MONTH(I2),1),DATE(YEAR(I2),MONTH(I2)+1,1)-1)
M=NETWORKDAYS(DATE(YEAR(I2),MONTH(I2),1),I2)
N=(MONTH(I2)-1)*K2
O=K2*M2/L2
P=N2+O2

Of course, this could be one formula

--o
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Guest]

Don,

This is close, I appreciate your insight. It looks as if you were having
difficulty in exactly the same way that I was. Based on the data in the
original example, the answer to the puzzle would appear something likes this:

Salary from 1/1/07 to 6/17/07 = $23,015.86
Salary from 6/18/07 to 8/4/07 = $7,363.64
Salary from 8/5/07 to 12/31/07 = $20,960.21
For a total salary for 2007 = $51,339.71

The salaries quoted in the example data are simply annual rates for those
periods and not necessarily what they were paid. Your reply seemed to
include an accumulation over each pay period. I see where you were breaking
out the partial months but the second segment where the beginning was during
a month and the end was during a month only provides one partial month amount
when in actuality there are two partial months amounts for that segment, one
for the beginning and one for the end.

I am going to try working through some logic given the formula examples you
provided however if you have any other insight, it would be appreciated.