Posted by Recently a client asked for a spreadsheet that would calculate employee overtime, employee regular-time and employee-doubletime based on California overtime laws. He gave me four rules to work with:
OT for anything over 8 hours in one day
OT for anything over 40 hours of regular-time for that week
(note that if they work 12 hours on day 1, they only have 8 hours of regular-time for the week)
DT for daily hours over 12 hours
DT for over 8 hours on Sunday if they worked 7 days that week
Keep in mind you have to have a number in each cell monday through sunday (the cells on row 2)- DO NOT LEAVE THESE CELLS BLANK, put a 0.
Here was my solution. If you have any questions or come up with any corrections, please let me know!
In order to get this into a spreadsheet, copy and paste this into excel, cell A1. You may see HTML below or you may see a table in your browser; either way, just copy and paste the content (into cell A1) and excel should do the rest!
<table>
<tr>
<td></td>
<td>Mon</td>
<td>Tues</td>
<td>Wed</td>
<td>Thurs</td>
<td>Fri</td>
<td>Sat</td>
<td>Sun</td>
</tr>
<tr>
<td>hours worked</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
<tr>
<td>REG</td>
<td>=MIN(8,B2)</td>
<td>=IF(SUM(B3:B3)<40,MIN(MIN(8,40-SUM(B3:B3)),C2),0)</td>
<td>=IF(SUM(B3:C3)<40,MIN(MIN(8,40-SUM(B3:C3)),D2),0)</td>
<td>=IF(SUM(B3:D3)<40,MIN(MIN(8,40-SUM(B3:D3)),E2),0)</td>
<td>=IF(SUM(B3:E3)<40,MIN(MIN(8,40-SUM(B3:E3)),F2),0)</td>
<td>=IF(SUM(B3:F3)<40,MIN(MIN(8,40-SUM(B3:F3)),G2),0)</td>
<td>=IF(AND(B2>0,C2>0,D2>0,E2>0,F2>0,G2>0,H2>0),0,IF(SUM(B3:G3)<40,MIN(MIN(8,40-SUM(B3:G3)),H2),0))</td>
</tr>
<tr>
<td>OT</td>
<td>=IF(AND(B3=0,B2>0),MIN(B2,MIN(12,B2)),IF(B2>8,MIN(B2-8,4),0))</td>
<td>=IF(AND(C3=0,C2>0),MIN(C2,MIN(12,C2)),IF(C2>8,MIN(C2-8,4),0))</td>
<td>=IF(AND(D3=0,D2>0),MIN(D2,MIN(12,D2)),IF(D2>8,MIN(D2-8,4),0))</td>
<td>=IF(AND(E3=0,E2>0),MIN(E2,MIN(12,E2)),IF(E2>8,MIN(E2-8,4),0))</td>
<td>=IF(AND(F3=0,F2>0),MIN(F2,MIN(12,F2)),IF(F2>8,MIN(F2-8,4),0))</td>
<td>=IF(AND(G3=0,G2>0),MIN(G2,MIN(12,G2)),IF(G2>8,MIN(G2-8,4),0))</td>
<td>=IF(AND(B2>0,C2>0,D2>0,E2>0,F2>0,G2>0,H2>0),MIN(8,H2),IF(AND(H3=0,H2>0),MIN(H2,MIN(12,H2)),IF(H2>8,MIN(H2-8,4),0)))</td>
</tr>
<tr>
<td>DT</td>
<td>=IF(B2>12,B2-12,0)</td>
<td>=IF(C2>12,C2-12,0)</td>
<td>=IF(D2>12,D2-12,0)</td>
<td>=IF(E2>12,E2-12,0)</td>
<td>=IF(F2>12,F2-12,0)</td>
<td>=IF(G2>12,G2-12,0)</td>
<td>=IF(AND(B2>0,C2>0,D2>0,E2>0,F2>0,G2>0,H2>0),IF(H2>8,H2-8,0),IF(H2>12,H2-12,0))</td>
</tr>
</table>
#1 by Jay on April 21, 2010 - 12:25 pm
Your overtime and double time formula for Saturday is wrong.
#2 by Louana on October 19, 2010 - 12:59 pm
The Saturday looks right to me, but Sunday is missing something… If you enter 14 hrs on Mon, Wed, Thur, Fri, 0 hrs on Tues, 6 hrs on Sat, and 8 hrs on Sun, it only shows the first 2 hrs on Sunday as regular hrs. It does not show the remaining 6 hrs as OT.
#3 by Olga Moen on October 28, 2010 - 12:17 pm
Copied the formula to cell a1. Entered days of week and time as instructed, but nothing happened. What am I doing wrong?
#4 by Mark Regneark on November 18, 2010 - 8:52 am
Looks interesting. I didn’t know this was possible.
#5 by David Gunning on December 19, 2010 - 7:39 pm
heres the corrected formula to use for the sunday overtime cell that will correct for the error mentioned above by Louana:
=IF(AND(B2>0,C2>0,D2>0,E2>0,F2>0,G2>0,H2>0),MIN(8,H2),IF(AND(H3=0,H2>0),MIN(H2,MIN(12,H2)),IF(H2>8,MIN(H2-8,4),IF(SUM(B3:H3)>39.99999,(H2-H3),0))))
#6 by Yossarian on January 13, 2011 - 10:26 pm
@David Gunning,
Mon=14,Tues=4,Wed=12,Thur=8 ,Fri=0 , Sat=10, Sun=15
The Sunday OT is still off by 4 hours.
#7 by Joe Lindsay on March 8, 2011 - 7:06 pm
Yosarian, you are incorrect here. Change Friday to 1 and watch the results. Your hours for Sunday will be all overtime: 8 @ 1.5 and 7 doubletime.
The law states that anything over 8 on Sunday (or whenever the last day of the pay period is) will be double only if you worked on all previous consecutive days. In your example, you worked zero on Friday. This holds true even if you called in sick on that day. You will be paid for 8 hours but you did not work them.
#8 by Joe Lindsay on March 8, 2011 - 7:16 pm
So here is Dwight Brown’s original spreadsheet with correction to Sunday OT from David Gunning. I verified and it seems to work a treat.
To answer Olga Moen’s question above, chose “Paste Special” and then select “Unicode Text”. Its always a good idea to past into Notepad first, then cut from Notepad into the target application when working with HTML!
Mon
Tues
Wed
Thurs
Fri
Sat
Sun
hours worked
0
0
0
0
0
0
0
REG
=MIN(8,B2)
=IF(SUM(B3:B3)<40,MIN(MIN(8,40-SUM(B3:B3)),C2),0)
=IF(SUM(B3:C3)<40,MIN(MIN(8,40-SUM(B3:C3)),D2),0)
=IF(SUM(B3:D3)<40,MIN(MIN(8,40-SUM(B3:D3)),E2),0)
=IF(SUM(B3:E3)<40,MIN(MIN(8,40-SUM(B3:E3)),F2),0)
=IF(SUM(B3:F3)<40,MIN(MIN(8,40-SUM(B3:F3)),G2),0)
=IF(AND(B2>0,C2>0,D2>0,E2>0,F2>0,G2>0,H2>0),0,IF(SUM(B3:G3)<40,MIN(MIN(8,40-SUM(B3:G3)),H2),0))
OT
=IF(AND(B3=0,B2>0),MIN(B2,MIN(12,B2)),IF(B2>8,MIN(B2-8,4),0))
=IF(AND(C3=0,C2>0),MIN(C2,MIN(12,C2)),IF(C2>8,MIN(C2-8,4),0))
=IF(AND(D3=0,D2>0),MIN(D2,MIN(12,D2)),IF(D2>8,MIN(D2-8,4),0))
=IF(AND(E3=0,E2>0),MIN(E2,MIN(12,E2)),IF(E2>8,MIN(E2-8,4),0))
=IF(AND(F3=0,F2>0),MIN(F2,MIN(12,F2)),IF(F2>8,MIN(F2-8,4),0))
=IF(AND(G3=0,G2>0),MIN(G2,MIN(12,G2)),IF(G2>8,MIN(G2-8,4),0))
=IF(AND(B2>0,C2>0,D2>0,E2>0,F2>0,G2>0,H2>0),MIN(8,H2),IF(AND(H3=0,H2>0),MIN(H2,MIN(12,H2)),IF(H2>8,MIN(H2-8,4),IF(SUM(B3:H3)>39.99999,(H2-H3),0))))
DT
=IF(B2>12,B2-12,0)
=IF(C2>12,C2-12,0)
=IF(D2>12,D2-12,0)
=IF(E2>12,E2-12,0)
=IF(F2>12,F2-12,0)
=IF(G2>12,G2-12,0)
=IF(AND(B2>0,C2>0,D2>0,E2>0,F2>0,G2>0,H2>0),IF(H2>8,H2-8,0),IF(H2>12,H2-12,0))