# Calculating Monthly Comp of New Employees based on Start Date (prorating first month)

1. ## Calculating Monthly Comp of New Employees based on Start Date (prorating first month)

Hi all - trying to calculate monthly comp for the year of new employees by prorating the first month based on date of hire. I want to be able to change the date of hire and have the formula calculate the appropriate comp for that month. Right now I have:

D36 = start date
E36 = end of starting month date
X4 = previous month in formula end date
Y4 = current month in formula end date
H26 = salary

=IF(\$D36>X\$4,0,IF((AND(\$D36<Y\$4,\$D36<\$E36)),(((EOMONTH(\$D36,0)-\$D36)/DAY(EOMONTH(\$D36,0))*\$H36)/12),\$H36/12

The problem I'm running into is that the if/and formula is always triggering the equation to take the fractional days. I need to figure a way to make it only do that for the first months, all subsequent months should be just comp divided by 12.

Thoughts?

2. ## Re: Calculating Monthly Comp of New Employees based on Start Date (prorating first month)

Hi and welcome.

I think you'll get a solution on this quicker if you can attach a sample workbook (not a picture of one) and show a handful of expected results.

BSB

3. ## Re: Calculating Monthly Comp of New Employees based on Start Date (prorating first month)

pro-rating all months should be fine, if you use dates correctly -- based on your outlined ranges:

Formula:
`Please Login or Register  to view this content.`

the above, copied to the right, should generate the periodic values requested.

There are currently 1 users browsing this thread. (0 members and 1 guests)