Calculate overtime in excel

[thomasks]

I need some help with a spreadsheet. I am trying to set up a sheet that will calculate union overtime grrrrrrrr!
Anyway, this is how it was explained to me.

"For example, if they worked a 12 hour day…it would be 8 hours @ regular time, 2 hours @ overtime and 2 hours @ double time. That's Monday - Saturday. Sunday is straight double-time. So I guess the equation would break down mon-sat only. "

I think I need to use some if and statements in excel but the double time is throwing a wrench in the works, and I am pulling my hair out now.
Any help would be appreciated.

 

[SkipVought]

Hi,

What do you have so far and where are you having trouble.

Generally ...
[tt]
if sunday then
doubletime = timeworked
else
if timeworked<= 8 then
straighttime = 8 - timeworked
else
straighttime = 8
doubletime = timeworked-8
end
end
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thomasks]

Well, I have been browsing the internet of all the timesheet excel examples and trying to edit them. (And here as well)Not working out when it comes to the double time.
I need to have columns with the days of the week (Mon- Sun) and the rows would be the employee names. the last three columns would be Total hours worked, Time and 1/2, Double time. Like I said the double time on Sunday, or over 8 hours worked any day of the week is messing it up.

 

[thomasks]

There is also the time and a half that has to be calculated for any hours worked on a weekday other than Sunday (always double time for Sunday) that is over 8 and less than 10.

Any hours worked on any day over 10 is double time.

Any hours worked on any day other than Sunday is time and a half if under 10 and more than 8.

Any hours worked on any day other than Sunday up to 8 is straight time.

40 hours is not a factor for calculating overtime.

clear as mud?

 

[AnotherHiggins]

This will give you a starting point. Let's say your date is in column A and hours worked are in Column B. Then in Column C you could put:

[tab][COLOR=blue white]=IF(WEEKDAY(A2) = 1, B2 * 2, IF(B2 <= TIMEVALUE("8:00:00"), B2, IF(B2 <= TIMEVALUE("10:00:00"), TIMEVALUE("8:00:00") + ((B2 - TIMEVALUE("8:00:00")) * 1.5), TIMEVALUE("11:00:00") + ((B2 - TIMEVALUE("10:00:00")) * 2))))[/color]
(Adjust the cell references accordingly)

That will produce the following results:
[tt]
Day & Date Hours Adjusted
Worked Hours
Sun, 06/29 7:45 15:30
Mon, 06/30 8:00 8:00
Tue, 07/01 9:00 9:30
Wed, 07/02 10:00 11:00
Thu, 07/03 11:00 13:00
Fri, 07/04 12:00 15:00
Sat, 07/05 13:00 17:00
Sun, 07/06 14:00 28:00
[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Well you're going to have to come up with a starting point. No example you'll find will have exactly what you want. You'll have to put some of your own sweat into it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thomasks]

I have put some effort into it.....
Here is where I am at using one column for Monday as an example.
Hours worked are in cell C2
in cell C3 is the formula: =IF(AND(C2>8,C2<10.1),"YES","NO") are the hours worked time and a half?
in cell C4 is the formula: =IF(C3="Yes",C2-8,0) if it is time and a half subtract straight time from it.
in cell C5 is the formula: =IF(AND(C3="NO",C2>10),(C2-8),0)if it is double time subtract straight time from it.
Then I have rows 3,4,5 hidden (the rows holding the logic)and I am trying to total the columns in J2: =SUM(C2:I2) total hours worked
in L2: =SUM(C4:H4) time and a half hours worked
in N2: =SUM(C5:I5) double time hours worked
The problem I run into is when you work more than 10 hours on a weekday (11 for example) the first 2 hours should be time and a half (after 8 but less than 10), and the last hour at double time. But I get 3 hours at double time.
I can't seem to attach the example since our corporate filter will not let me access box.net to store the file...

 

[SkipVought]

"The problem I run into is when you work more than 10 hours on a weekday (11 for example) the first 2 hours should be time and a half (after 8 but less than 10), and the last hour at double time. But I get 3 hours at double time."

What's your formula?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

*ahem*
Did you look at my post, thomasks? It isn't custom-fit to your spreadsheet, either, but it provides a formula that should serve as a good jumping-off point.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[thomasks]

for double time it is:
=IF(AND(C3="NO",C2>10),(C2-8),0)

 

[SkipVought]

...and there's nothing like knowing what values are in the cells you are referencing.

maybe posting an example of the data you're working with???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thomasks]

Well, like I said before, I can't seem to post the actual spreadsheet. but the data is outlined like this:

column headers
A1 - Employee name (data entered by employee)
B1 - Mon hours (data entered by employee)
C1 - Tue hours (data entered by employee)
D1 - Wed hours (data entered by employee)
E1 - Thu hours (data entered by employee)
F1 - Fri hours (data entered by employee)
G1 - Sat hours (data entered by employee)
H1 - Sun hours (data entered by employee)

I1 - Straight time worked (formula result)
J1 - Time and a half hours (formula result)
K1- Double time worked (formula result)

Rows 2 - whatever are the data and formula result.

 

[AnotherHiggins]

Right.

So I'll just be leaving then.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[thomasks]

John,
I tried your solution and it looks pretty good other than I always get time and a half unless it is Sunday.
Any idea why?
And thanks for your time.

 

[AnotherHiggins]

The formula as posted depends on two things:
[tab]1) The Day is actually a date formatted to look like a day
[tab]2) The times are actual times, not "8" or "10.5"

If those conditions aren't met, you can make these changes to the formula:

[tab]1) Change the first part of the formula to "IF(A2 = "Sun""
[tab]2) Replace all of the TimeValue functions with numbers. Example: "TIMEVALUE("8:00:00")" will become "8" (without quotes)

Keep in mind that this would be used each day to come up with the number of "equivalent hours" worked. That is, the number of hours that would be paid at the straight-time rate in order to properly reimburse the employees. In your example, this would be on Row 3, then you would simply Sum all of the cells.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Any timekeeping system I've seen, have data entry forms that SEPARATE straight, overtime, vacation, sick, berevement, jury duty, family leave, etc.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

anotherhiggins, just for a laugh, here's another version of your original adjusted-hours equation (without the "if" for handling Sundays):

=A5+((SIGN(A5-TIME(8, 0, 0))+1)*(A5-TIME(8, 0, 0))*0.5/2)+((SIGN(A5-TIME(10, 0, 0))+1)*(A5-TIME(10, 0, 0))*0.5/2)

(A5 is merely the cell in which I happened to have my unadjusted time)

 

[thomasks]

Ok here is what a coworker has come up with, and it appears to be working:

Straight time:
=IF(E4<=8,E4*1,"8")+IF(F4<=8,F4*1,"8")+IF(G4<=8,G4*1,"8")+IF(H4<=8,H4*1,"8")
+IF(I4<=8,I4*1,"8")+IF(J4<=8,J4*1,"8")

Time and a half:
=IF(E4<8,"0",IF(E4<=10,E4-8,IF(E4>10,"2")))+IF(F4<8,"0",IF(F4<=10,F4-8,IF
(F4>10,"2")))+IF(G4<8,"0",IF(G4<=10,G4-8,IF(G4>10,"2")))+IF(H4<8,"0",
IF(H4<=10,H4-8,IF(H4>10,"2")))+IF(I4<8,"0",IF(I4<=10,I4-8,IF(I4>10,"2")))
+IF(J4<8,"0",IF(J4<=10,J4-8,IF(J4>10,"2")))

Double time:
=SUM(E4:K4)-(L4+M4)

Total hours worked:
=SUM(E4:K4)

 

[SkipVought]

I have THREE rows under the time for the employee for straight time, time and a half and double time...
[tt]
ST: =IF(B2<=8,B2,8)
TH: =IF(AND(B2>8,B2<=10),B2-8,IF(B2>10,2,0))
DT: =IF(B2>10,B2-10,0)
[/tt]
Naturally Sunday's formula is ONLY DT for all hours.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Just for fun, here are formulas that will divvy up the whole week at once, rather than by day, in keeping with the layout from thomasks' post date stamped 2 Jul 08 11:25,

In I2:
=SUMIF(B2:G2, "<=" & TIMEVALUE("8:00:00")) + COUNTIF(B2:G2, ">" & TIMEVALUE("8:00:00")) * TIMEVALUE("8:00:00")

In J2:
=SUMPRODUCT((B2:G2 > TIMEVALUE("8:00:00")) * (B2:G2 <= TIMEVALUE("10:00:00")) * (B2:G2)) + COUNTIF(B2:G2, ">" & TIMEVALUE("10:00:00")) * TIMEVALUE("2:00:00") - (SUMPRODUCT((B2:G2 > TIMEVALUE("8:00:00")) * (B2:G2 <= TIMEVALUE("10:00:00"))) * TIMEVALUE("8:00:00"))

In K2:
=SUMPRODUCT((B2:G2 > TIMEVALUE("10:00:00")) * (B2:G2)) - (COUNTIF(B2:G2,">"&TIMEVALUE("10:00:00")) * TIMEVALUE("10:00:00")) + H2

I know improvements could be made, especially with array formulas...

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.