complex overtime/late shift compensation calculations

[Oslopelle]

I'm running a 24 hour store and the calculation of staff hours drives
me insane.
I have employees that work by the hour, in full 100% and in part time
%. The employees that does not work by the hour have fixed work-times
on a schedule so that the hours will add up to 39*52*percentage of
full time employment hours for a year. Some weeks a person might work
many hours and are compensated for it the next week when they work
fewer hours.

First the staff gets compensation for working late:
Week-days
50% on every minute between 17:00-21:00 weekdays
100% on every minute between 21:00-4:00 weekdays
50% on every minute between 4:00-8:00 weekdays
Normal hours between 8:00-17:00

Saturdays:
Normal hours between 8:00-13:00
50% on every minute between 13:00-21:00 Saturdays
100% on every minute between 21:00-4:00 Saturdays
50% on every minute 4:00-8:00 Saturdays

Sundays
50% on every minute 4:00-21:00
100%on every minute between 21:00-4:00

Eves (like Christmas eve)
50% on every minute between 4:00-13:00
100% on every minute between 13:00-4:00

Holidays
100% on every minute the whole day

Conditions for overtime
Also the staff gets overtime if they work more than 39 hours a week or
more than 9 hours a day if the change is initiated by the employer.
They will not get double compensations, they will get either
compensation for working late or overtime, whichever is the highest.

Part-time employees
For any extra shifts initiated by the employer in addition to the
shifts on the full year plan up to an added 39 hours for the week
there is an addition of 30% for every minute between 8-17 on weekdays
and 8-13 Saturdays, and 100% after 17:00 on weekdays and after 13:00
on Saturdays.

For full time employees, part time employees that has worked 39 hours
in a week
Weekdays 8-21 is compensated with 50% extra
Weekdays 21-24 is compensated with 100% extra
Saturdays 8-13 is compensated 50% extra
Saturdays 13-24 is compensated 100% extra
Sundays is compensated 100%extra the whole day

So...
For example if i tell a person to change times from an evening shift
to a day-shift in a week on a weekday where the employee works 39
hours already, the employee should get overtime compensation for the
CHANGE from the previous agreed upon time that surpasses 39 hours or 9
hours a day.
The employee was supposed to work 16:00-22:00 (6hrs) and i tell her to
work 12:00-19:00 (7hrs) the employee should get 1 hour overtime ( 50%
extra) in the period between 12:00-16:00. This is important since the
employee will get compensation for working late after 17:00. This
employee should get 1 hour overtime compensation somewhere in the
period 12-16 and 50% compensation for working late in the period
17-19.

If the employee will get an unscheduled free day later in the week,
there will be no overtime for this day as the summed up hours for the
week will not surpass 39 hours.

Also, overtime should not me a factor if two employees agree to change
work-times with each other. It should only be a factor when initiated
by the employer!

Any thoughts on how to create a book that takes all these variables
into effect?

I have constructed a book that will calculate the compensations for
working late but the overtime is killing me.

 

[Martin Fishlock]

Dea Oslo Pelle

I would actually write a VBA macro to deal with this and use some rule based
system to pick up all the exceptions. For example the situation when two
staff agree to switch is difficult to program and checking to ensure that
they comply. Also the problem of OT in those situations is complex. But
basically you need to build up some method of documenting the rules so that
you can just enter the times in the computer and get the pay out at the end.

Personally payroll tends to lend itself to more traditional programming or
database work than excel.

But good luck.

 

[Herbert Seidenberg]

You might adapt this to your needs:
http://www.freefilehosting.net/download/3f0f4