B
beachrog
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.
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.