LinkBack URL
About LinkBacks
Hello,
I need to calculate daily (over 8hrs) and weekly (over 40hrs) overtime for the week, and am having trouble with this.
I have attached a copy of my template.
Can anyone help?
thankyou,
Hello,
I need to calculate daily (over 8hrs) and weekly (over 40hrs) overtime for the week, and am having trouble with this.
I have attached a copy of my template.
Can anyone help?
thankyou,
What exactly is the problem?
Your total in AC is wrong as you cannot simply SUM columns D to Y as those columns include totals
something like ..
=SUMIF(D5:Y5,"REG",D8:Y8)
HI,
Trying to get my ot to calculate automatically anything over 8hr/day AND 40 hours per week.
sorry beginner excel skills.
AC is used for stat averaging not calculating ot
I need a clarification on overtime:
Suppose a person works the following hours M-T-W-T-F-S: 4-9-8-8-8-2 That's a total of 39 hours so no overtime due to going over 40 hours. However there are 9 hours worked on Tuesday, so one hour of overtime for going over 8 hours on Tuesday: 39 Regular Hours + 1 OT Hour.
Now suppose it's 8 hours each day Mon-Sat. That's 48 hours so 8 hours of OT for going over 40 Hrs per week.
Now suppose the hours worked are 8-9-7-8-8-8. Than's 48 hours so 8 hours OT for going over 40 hours, but the person worked 9 hours on Tuesday. So is it 9 hours of overtime or 8 hours of overtime. Does the extra hour on Tuesday "double count?"
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
yes, the extra hour on tuesday is double counted.
Regular hours
=MIN(40,J8)
OT
=J8-K8+(SUMPRODUCT((D8:I8>8)*(D8:I8-8)))
The latter adds any daily hours over 8 to the OT total
so using Dflak's example of 8-9-7-8-8-8
Total hours 48
OT = 48 - 40+ 1 (the extra hour on the 9 hour day)
Re AC total : it is still wrong however you use it as it triple counts the hours.
Thank you so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks