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?
Bookmarks