Pro-rating salary into months, and applying increment values

Hello all,

I am new to the forum and have been searching through various posts this morning, but I cant seem to either find a full answer, or apply what I'm being told correctly!

I am looking to produce a monthly basic pay figure per employee, which will use the start and end date on the row in order to pro-rate the annual salary into the month based on these dates.

For example, for an employee with an employment period of 01/08/X1 (or prior) - 31/07/X2, I would want to show 1/12th of the salary in each month. But for an employee with an employment period of 01/08/X1 (or prior) - 27/03/X2, I would want to show salary up to and including 27/03.

I could then use this factoring of dates to calculate the cost of contract changes, using their effective dates.

To further complicate this, staff are eligible for a pay rise based on the below:
- If the member of staff has 6 months service in their current post on 1st September, they are eligible to be moved up to the next pay point from 1st September, which is applied in the November payroll and backdated to 1st September.
- If the member of staff does not have 6 month's service in their current post on 1st September, but are employed on the 1st September, they are eligible to be moved up to the next pay point 6 months following their post start date.
- If the member of staff does not have 6 months service, and was not employed on 1st September, they are ineligible.

I have attached my workings from this morning (for which I have managed to do a small amount of this), but I cant figure out the best way to include all of this, if it is even possible!

Any hints or advice would be greatly greatly appreciated!

Regards,
Michael

I think you just miss something. Try doing it manually. Sometimes it works.

Hi both,

Many thanks for your responses, I have managed to get it to calculate a monthly amount based on the start and end date of the contract, but I am now having problems applying the increments. If the increments are applied from 1st September in the December payroll (November in my original post), then it is easy as it is just a 1/12th difference, the problem is in applying it to those members of staff who are eligible +6 months from their start date.

Would you be able to advise how best to attack this?

Thanks,
Mike