Need help with an hour calculating timesheet... please!

Employee Timecard AB.xlsx

I have hourly employees who work in a province that has certain OT regulations. The way the excel sheet worked is that you put in your hours under the cost center, and it should auto calculate overtime, total hours, etc.

Rules: Overtime must be paid after 8 hours worked in any given day, AND 44 hours total in a week.

For instance: an employee can work M-F, 8 hours a day except for Friday in which they work 10 hours. Total hours to be paid regular would be 40, and 2 hours of OT (10-8=2).

Alternatively, an employee can work the following:
M: 8h (8 reg)
T: 8h (8 reg)
W: 8h (8 reg)
Th: 8h (8 reg)
F: 10h (8 reg, 2 OT)
S: 8h (4 reg, 4 OT)
Su:8h (8 OT)

Now Ive inherited the above sheet, and seems to work but does not calculate anything on sunday

need some serious help fixing this - am a novice so definitely need direction please
thanks!

Ive played with this, if you hit more than 4 overtime hours before sunday, sunday wont populate at all.

I've even tried changing some of the hidden formulas to get it to work and came up with no real logical solution. I'm Sorry. Good luck, im sure there is some genius on here who knows more than i do.

Yeah I tried a bunch and cant get anywhere, not smart enough suppose

see attached, sunday wont populate when you throw 8 hours in every day

seems to be an issue with sunday?

Employee Timecard AB.xlsx

how can i fix this? the logic probably was that the maximum number of hours regular is 44, then anything after that is OT anyway., as per my examples above.
i inherited the spreadsheet but would liek to make it work...
thanks...

I played with the if formulas to first check if hours for the day will go make cumulative hours for week to go over 44, then a second check to see if total hours for day are greater than 8...I think this is working for calculating overtime per your requirements. Does it work for you?

OMG we are SO close

take a look at this one though from your sheet.
The last 4 hours should be regular hours, not split between 2/2
regular hours need to go up to 44
new timecard AB.xlsx

I may be misunderstanding your requirements, but are you sure the last 4 hours (worked on Saturday) should not be split between regular and overtime hours? Your criteria for overtime is when an employee works more than 8 hours in a day or more than 44 hours in a week. If we look at the hours worked from Monday to Friday (8+8+8+10+8) that's 42 hours. On Saturday, the first two hours are regular time, but then the employee has worked 44 hours in that week, so the next two hours, and any time after that, is overtime.

no thats 40 hours of regular, we're talking regular vs overtime. here is the fact sheet from the government: http://work.alberta.ca/documents/Ove...ertime-Pay.pdf

and a screenshot of how our payroll processes it by those standards

those 2 hours dont count towards the 44, does that make sense, since they are being paid at OT?

pic.png

Like this?