Overtime with different rate of pay

[kkqazi]

hi im a beginner in excel and i need an overtime sheet which calculates the hours of

employees with standard and over time rate. But the hardest part for me is that

there are so many conditions which is confusing me.

1)Week start from Sunday to Saturday
2)standard hours are 40/week
3) if it goes above 40 they are entitled for an overtime
4)now there are different rates of hours.
5)std day rate, std night rate, std day overtime rate, std night overtime rate,

weekend day rate, weekend night rate, weekend day o/t rate and weekend o/t night

rate.

The hours can start from any day and finish by saturday.
would like to calculate if
1) they work on days, they get paid for days.
2) if they work for nights, then get paid for nights.
3) if the work in the middle they should get paid both night and day mixture
4)as soon the hours hit above 40 , the o/t starts.
5)only those hours need to be calculated for overtime which are after 40 hours. Now

the hard part is , they can be days, nights , or weekends.
I am so confused as I dont know how to get it right.

Any help will be appreciated.

[MarvinP]

Hi kkqazi and welcome to the forum,

Do you have a worksheet that you need to follow when putting in your data yet? If there is, you should show us an example so any help is more appropriate. Click on "Go Advanced" and then the PaperClip Icon above the message area to upload/attach a sample file.

Questions -
1. Does each employee have a starting base pay that is different from the others?
2. Does a workweek always start at Midnight on Sunday?
3. Do you have a In and Out timecard for each employee? Is that how you gather data?
4. Can a worker work half day and half night on the same shift? Different rates for same shift?
5. Is a Weekend shift always start on Sat or Sun or can it start on Fri and end in Sat?

I have a method to solve these kinds of problems that are overwhelming. It comes from an old statement of "The worlds fastest potato peeler, peels them one at a time". Which means - Take a single worker and create a way to calculate there pay for a normal week. Then solve the problem if he gets more than 40 total hours. Then ... Keep adding to these smaller individual problems until you get a greater answer.

HINT - use the function called Weekday() which returns a 1 = Sunday and 7 for Saturday. I believe you will need to use this to determine weekend.