Unable to calculate overtime for multiple employees on a single spreadsheet.

J

jamesewells

New Member
Joined
Mar 1, 2016
Messages
1
I am trying to calculate overtime by week for my employees in an Excel 2010 spreadsheet. I've figured out, I think, how to do the first employee, Ms. Battles, as it calculates correctly, but am having trouble with the other 2. There are actually about 20 employees total. The column at the top with the dates are the workweek. Anything over 40 in any workweek is overtime. It seems I need to reset the counters when the employee changes, but I'm not sure. I am basically looking for a formula I can drag down the spreadsheet, after it is sorted by employee, and have the regular time and overtime correctly calculated. Any and all suggestions are appreciated. Thanks! Jim Wells

Excel 2010
ABCDEFGHIJKLMNO
1Pay DateEmployee Name1Employee Name2Client Name1Client Name2Service DateStart TimeEnd TimeWorked Hours2/6/162/13/162/20/16Reg. HoursOt HoursDay
22/22/16BarbaraBattlesStephanieCutchen2/1/168:00 AM8:00 AM6.256.250.000.006.250.00Mon
32/22/16BarbaraBattlesStephanieCutchen2/2/168:00 AM8:00 AM6.256.250.000.006.250.00Tue
42/22/16BarbaraBattlesStephanieCutchen2/3/168:00 AM8:00 AM6.256.250.000.006.250.00Wed
52/22/16BarbaraBattlesStephanieCutchen2/4/168:00 AM8:00 AM6.256.250.000.006.250.00Thu
62/22/16BarbaraBattlesStephanieCutchen2/5/168:00 AM8:00 AM6.256.250.000.006.250.00Fri
72/22/16BarbaraBattlesStephanieCutchen2/6/168:00 AM8:00 AM16.0016.000.000.008.757.25Sat
82/22/16BarbaraBattlesStephanieCutchen2/7/168:00 AM8:00 AM16.000.0016.000.0016.000.00Sun
92/22/16BarbaraBattlesStephanieCutchen2/8/168:00 AM8:00 AM6.250.006.250.006.250.00Mon
102/22/16BarbaraBattlesStephanieCutchen2/9/168:00 AM8:00 AM6.250.006.250.006.250.00Tue
112/22/16BarbaraBattlesStephanieCutchen2/10/168:00 AM8:00 AM6.250.006.250.006.250.00Wed
122/22/16BarbaraBattlesStephanieCutchen2/11/168:00 AM8:00 AM6.250.006.250.005.251.00Thu
132/22/16BarbaraBattlesStephanieCutchen2/12/168:00 AM8:00 AM6.250.006.250.000.006.25Fri
142/22/16BarbaraBattlesStephanieCutchen2/13/168:00 AM8:00 AM16.000.0016.000.000.0016.00Sat
152/22/16BarbaraBattlesStephanieCutchen2/14/168:00 AM8:00 AM16.000.000.0016.0016.000.00Sun
162/22/16BarbaraBattlesStephanieCutchen2/15/168:00 AM8:00 AM6.250.000.006.256.250.00Mon
172/22/16AprilBedneauRuthOgburn2/3/168:00 AM8:00 AM8.008.000.000.008.000.00Wed
182/22/16AprilBedneauThomasBarnett2/14/1612:45 PM5:00 PM4.250.000.004.254.254.25Sun
192/22/16AprilBedneauAnn MarieBrinkman2/14/168:00 AM8:00 AM8.000.000.008.008.000.00Sun
202/22/16AprilBedneauCandasieKnight2/16/168:00 AM8:00 AM5.000.000.005.005.000.00Tue
212/22/16AprilBedneauCynthiaCorg2/15/168:00 AM10:00 AM2.000.000.002.000.502.00Mon
222/22/16AprilBedneauCynthiaCraig2/10/168:00 AM8:00 AM5.000.005.000.000.000.00Wed
232/22/16AprilBedneauRuthOgburn2/11/168:00 AM8:00 AM8.000.008.000.000.000.00Thu
242/22/16AprilBedneauThomasBarnett2/12/161:00 PM5:00 PM4.000.004.000.000.004.00Fri
252/22/16KhadijahBarnesLillieThomas2/5/168:00 AM8:00 AM16.0016.000.000.000.000.00Fri
262/22/16KhadijahBarnesLillieThomas2/6/168:00 AM8:00 AM16.0016.000.000.000.000.00Sat
272/22/16KhadijahBarnesLillieThomas2/7/168:00 AM5:00 PM16.000.0016.000.000.000.00Sun
282/22/16KhadijahBarnesLillieThomas2/12/168:00 AM8:00 AM16.000.0016.000.000.000.00Fri
292/22/16KhadijahBarnesLillieThomas2/13/168:00 AM8:00 AM16.000.0016.000.000.000.00Sat
302/22/16KhadijahBarnesLillieThomas2/14/168:00 AM8:00 AM16.000.000.0016.000.000.00Sun

<tbody>
</tbody>
Feb. 22​

Worksheet Formulas
CellFormula
J2=IF(F2<=J$1,I2,0)
K2=IF(AND(F2>J$1,F2<=K$1),I2,0)
L2=IF(AND(F2>K$1,F2<=L$1),I2,0)
M2=(MAX(0,MIN(J2,40-SUM($J29:J$29)))+(MAX(0,MIN(K2,40-SUM($K29:K$29)))+(MAX(0,MIN(L2,40-SUM($L29:L$29))))))
N2=MAX(0,MIN(J2-M2,SUM($M$2:M2)))+MAX(0,MIN(K2-M2,SUM($M$2:M2)))+MAX(0,MIN(L2-M2,SUM($M$2:M2)))
O2=TEXT(WEEKDAY(F2), "ddd")
J3=IF(F3<=J$1,I3,0)
K3=IF(AND(F3>J$1,F3<=K$1),I3,0)
L3=IF(AND(F3>K$1,F3<=L$1),I3,0)
M3=(MAX(0,MIN(J3,40-SUM($J$2:J2)))+(MAX(0,MIN(K3,40-SUM($K$2:K2)))+(MAX(0,MIN(L3,40-SUM($L$2:L2))))))
N3=MAX(0,MIN(J3-M3,SUM($M$2:M3)))+MAX(0,MIN(K3-M3,SUM($M$2:M3)))+MAX(0,MIN(L3-M3,SUM($M$2:M3)))
O3=TEXT(WEEKDAY(F3), "ddd")
J4=IF(F4<=J$1,I4,0)
K4=IF(AND(F4>J$1,F4<=K$1),I4,0)
L4=IF(AND(F4>K$1,F4<=L$1),I4,0)
M4=(MAX(0,MIN(J4,40-SUM($J$2:J3)))+(MAX(0,MIN(K4,40-SUM($K$2:K3)))+(MAX(0,MIN(L4,40-SUM($L$2:L3))))))
N4=MAX(0,MIN(J4-M4,SUM($M$2:M4)))+MAX(0,MIN(K4-M4,SUM($M$2:M4)))+MAX(0,MIN(L4-M4,SUM($M$2:M4)))
O4=TEXT(WEEKDAY(F4), "ddd")
J5=IF(F5<=J$1,I5,0)
K5=IF(AND(F5>J$1,F5<=K$1),I5,0)
L5=IF(AND(F5>K$1,F5<=L$1),I5,0)
M5=(MAX(0,MIN(J5,40-SUM($J$2:J4)))+(MAX(0,MIN(K5,40-SUM($K$2:K4)))+(MAX(0,MIN(L5,40-SUM($L$2:L4))))))
N5=MAX(0,MIN(J5-M5,SUM($M$2:M5)))+MAX(0,MIN(K5-M5,SUM($M$2:M5)))+MAX(0,MIN(L5-M5,SUM($M$2:M5)))
O5=TEXT(WEEKDAY(F5), "ddd")
J6=IF(F6<=J$1,I6,0)
K6=IF(AND(F6>J$1,F6<=K$1),I6,0)
L6=IF(AND(F6>K$1,F6<=L$1),I6,0)
M6=(MAX(0,MIN(J6,40-SUM($J$2:J5)))+(MAX(0,MIN(K6,40-SUM($K$2:K5)))+(MAX(0,MIN(L6,40-SUM($L$2:L5))))))
N6=MAX(0,MIN(J6-M6,SUM($M$2:M6)))+MAX(0,MIN(K6-M6,SUM($M$2:M6)))+MAX(0,MIN(L6-M6,SUM($M$2:M6)))
O6=TEXT(WEEKDAY(F6), "ddd")
J7=IF(F7<=J$1,I7,0)
K7=IF(AND(F7>J$1,F7<=K$1),I7,0)
L7=IF(AND(F7>K$1,F7<=L$1),I7,0)
M7=(MAX(0,MIN(J7,40-SUM($J$2:J6)))+(MAX(0,MIN(K7,40-SUM($K$2:K6)))+(MAX(0,MIN(L7,40-SUM($L$2:L6))))))
N7=MAX(0,MIN(J7-M7,SUM($M$2:M7)))+MAX(0,MIN(K7-M7,SUM($M$2:M7)))+MAX(0,MIN(L7-M7,SUM($M$2:M7)))
O7=TEXT(WEEKDAY(F7), "ddd")
J8=IF(F8<=J$1,I8,0)
K8=IF(AND(F8>J$1,F8<=K$1),I8,0)
L8=IF(AND(F8>K$1,F8<=L$1),I8,0)
M8=(MAX(0,MIN(J8,40-SUM($J$2:J7)))+(MAX(0,MIN(K8,40-SUM($K$2:K7)))+(MAX(0,MIN(L8,40-SUM($L$2:L7))))))
N8=MAX(0,MIN(J8-M8,SUM($M$2:M8)))+MAX(0,MIN(K8-M8,SUM($M$2:M8)))+MAX(0,MIN(L8-M8,SUM($M$2:M8)))
O8=TEXT(WEEKDAY(F8), "ddd")
J9=IF(F9<=J$1,I9,0)
K9=IF(AND(F9>J$1,F9<=K$1),I9,0)
L9=IF(AND(F9>K$1,F9<=L$1),I9,0)
M9=(MAX(0,MIN(J9,40-SUM($J$2:J8)))+(MAX(0,MIN(K9,40-SUM($K$2:K8)))+(MAX(0,MIN(L9,40-SUM($L$2:L8))))))
N9=MAX(0,MIN(J9-M9,SUM($M$2:M9)))+MAX(0,MIN(K9-M9,SUM($M$2:M9)))+MAX(0,MIN(L9-M9,SUM($M$2:M9)))
O9=TEXT(WEEKDAY(F9), "ddd")
J10=IF(F10<=J$1,I10,0)
K10=IF(AND(F10>J$1,F10<=K$1),I10,0)
L10=IF(AND(F10>K$1,F10<=L$1),I10,0)
M10=(MAX(0,MIN(J10,40-SUM($J$2:J9)))+(MAX(0,MIN(K10,40-SUM($K$2:K9)))+(MAX(0,MIN(L10,40-SUM($L$2:L9))))))
N10=MAX(0,MIN(J10-M10,SUM($M$2:M10)))+MAX(0,MIN(K10-M10,SUM($M$2:M10)))+MAX(0,MIN(L10-M10,SUM($M$2:M10)))
O10=TEXT(WEEKDAY(F10), "ddd")
J11=IF(F11<=J$1,I11,0)
K11=IF(AND(F11>J$1,F11<=K$1),I11,0)
L11=IF(AND(F11>K$1,F11<=L$1),I11,0)
M11=(MAX(0,MIN(J11,40-SUM($J$2:J10)))+(MAX(0,MIN(K11,40-SUM($K$2:K10)))+(MAX(0,MIN(L11,40-SUM($L$2:L10))))))
N11=MAX(0,MIN(J11-M11,SUM($M$2:M11)))+MAX(0,MIN(K11-M11,SUM($M$2:M11)))+MAX(0,MIN(L11-M11,SUM($M$2:M11)))
O11=TEXT(WEEKDAY(F11), "ddd")
J12=IF(F12<=J$1,I12,0)
K12=IF(AND(F12>J$1,F12<=K$1),I12,0)
L12=IF(AND(F12>K$1,F12<=L$1),I12,0)
M12=(MAX(0,MIN(J12,40-SUM($J$2:J11)))+(MAX(0,MIN(K12,40-SUM($K$2:K11)))+(MAX(0,MIN(L12,40-SUM($L$2:L11))))))
N12=MAX(0,MIN(J12-M12,SUM($M$2:M12)))+MAX(0,MIN(K12-M12,SUM($M$2:M12)))+MAX(0,MIN(L12-M12,SUM($M$2:M12)))
O12=TEXT(WEEKDAY(F12), "ddd")
J13=IF(F13<=J$1,I13,0)
K13=IF(AND(F13>J$1,F13<=K$1),I13,0)
L13=IF(AND(F13>K$1,F13<=L$1),I13,0)
M13=(MAX(0,MIN(J13,40-SUM($J$2:J12)))+(MAX(0,MIN(K13,40-SUM($K$2:K12)))+(MAX(0,MIN(L13,40-SUM($L$2:L12))))))
N13=MAX(0,MIN(J13-M13,SUM($M$2:M13)))+MAX(0,MIN(K13-M13,SUM($M$2:M13)))+MAX(0,MIN(L13-M13,SUM($M$2:M13)))
O13=TEXT(WEEKDAY(F13), "ddd")
J14=IF(F14<=J$1,I14,0)
K14=IF(AND(F14>J$1,F14<=K$1),I14,0)
L14=IF(AND(F14>K$1,F14<=L$1),I14,0)
M14=(MAX(0,MIN(J14,40-SUM($J$2:J13)))+(MAX(0,MIN(K14,40-SUM($K$2:K13)))+(MAX(0,MIN(L14,40-SUM($L$2:L13))))))
N14=MAX(0,MIN(J14-M14,SUM($M$2:M14)))+MAX(0,MIN(K14-M14,SUM($M$2:M14)))+MAX(0,MIN(L14-M14,SUM($M$2:M14)))
O14=TEXT(WEEKDAY(F14), "ddd")
J15=IF(F15<=J$1,I15,0)
K15=IF(AND(F15>J$1,F15<=K$1),I15,0)
L15=IF(AND(F15>K$1,F15<=L$1),I15,0)
M15=(MAX(0,MIN(J15,40-SUM($J$2:J14)))+(MAX(0,MIN(K15,40-SUM($K$2:K14)))+(MAX(0,MIN(L15,40-SUM($L$2:L14))))))
N15=MAX(0,MIN(J15-M15,SUM($M$2:M15)))+MAX(0,MIN(K15-M15,SUM($M$2:M15)))+MAX(0,MIN(L15-M15,SUM($M$2:M15)))
O15=TEXT(WEEKDAY(F15), "ddd")
J16=IF(F16<=J$1,I16,0)
K16=IF(AND(F16>J$1,F16<=K$1),I16,0)
L16=IF(AND(F16>K$1,F16<=L$1),I16,0)
M16=(MAX(0,MIN(J16,40-SUM($J$2:J15)))+(MAX(0,MIN(K16,40-SUM($K$2:K15)))+(MAX(0,MIN(L16,40-SUM($L$2:L15))))))
N16=MAX(0,MIN(J16-M16,SUM($M$2:M16)))+MAX(0,MIN(K16-M16,SUM($M$2:M16)))+MAX(0,MIN(L16-M16,SUM($M$2:M16)))
O16=TEXT(WEEKDAY(F16), "ddd")
J17=IF(F17<=J$1,I17,0)
K17=IF(AND(F17>J$1,F17<=K$1),I17,0)
L17=IF(AND(F17>K$1,F17<=L$1),I17,0)
M17=(MAX(0,MIN(J17,40-SUM($J$29:J29)))+(MAX(0,MIN(K17,40-SUM($K$29:K29)))+(MAX(0,MIN(L17,40-SUM($L$29:L29))))))
N17= 24*(H17-G17)
O17=TEXT(WEEKDAY(F17), "ddd")
J18=IF(F18<=J$1,I18,0)
K18=IF(AND(F18>J$1,F18<=K$1),I18,0)
L18=IF(AND(F18>K$1,F18<=L$1),I18,0)
M18=(MAX(0,MIN(J18,40-SUM($J$2:J17)))+(MAX(0,MIN(K18,40-SUM($K$2:K17)))+(MAX(0,MIN(L18,40-SUM($L$2:L17))))))
N18= 24*(H18-G18)
O18=TEXT(WEEKDAY(F18), "ddd")
J19=IF(F19<=J$1,I19,0)
K19=IF(AND(F19>J$1,F19<=K$1),I19,0)
L19=IF(AND(F19>K$1,F19<=L$1),I19,0)
M19=(MAX(0,MIN(J19,40-SUM($J$2:J18)))+(MAX(0,MIN(K19,40-SUM($K$2:K18)))+(MAX(0,MIN(L19,40-SUM($L$2:L18))))))
N19= 24*(H19-G19)
O19=TEXT(WEEKDAY(F19), "ddd")
J20=IF(F20<=J$1,I20,0)
K20=IF(AND(F20>J$1,F20<=K$1),I20,0)
L20=IF(AND(F20>K$1,F20<=L$1),I20,0)
M20=(MAX(0,MIN(J20,40-SUM($J$2:J19)))+(MAX(0,MIN(K20,40-SUM($K$2:K19)))+(MAX(0,MIN(L20,40-SUM($L$2:L19))))))
N20= 24*(H20-G20)
O20=TEXT(WEEKDAY(F20), "ddd")
J21=IF(F21<=J$1,I21,0)
K21=IF(AND(F21>J$1,F21<=K$1),I21,0)
L21=IF(AND(F21>K$1,F21<=L$1),I21,0)
M21=(MAX(0,MIN(J21,40-SUM($J$2:J20)))+(MAX(0,MIN(K21,40-SUM($K$2:K20)))+(MAX(0,MIN(L21,40-SUM($L$2:L20))))))
N21= 24*(H21-G21)
O21=TEXT(WEEKDAY(F21), "ddd")
J22=IF(F22<=J$1,I22,0)
K22=IF(AND(F22>J$1,F22<=K$1),I22,0)
L22=IF(AND(F22>K$1,F22<=L$1),I22,0)
M22=(MAX(0,MIN(J22,40-SUM($J$2:J21)))+(MAX(0,MIN(K22,40-SUM($K$2:K21)))+(MAX(0,MIN(L22,40-SUM($L$2:L21))))))
N22= 24*(H22-G22)
O22=TEXT(WEEKDAY(F22), "ddd")
J23=IF(F23<=J$1,I23,0)
K23=IF(AND(F23>J$1,F23<=K$1),I23,0)
L23=IF(AND(F23>K$1,F23<=L$1),I23,0)
M23=(MAX(0,MIN(J23,40-SUM($J$2:J22)))+(MAX(0,MIN(K23,40-SUM($K$2:K22)))+(MAX(0,MIN(L23,40-SUM($L$2:L22))))))
N23= 24*(H23-G23)
O23=TEXT(WEEKDAY(F23), "ddd")
J24=IF(F24<=J$1,I24,0)
K24=IF(AND(F24>J$1,F24<=K$1),I24,0)
L24=IF(AND(F24>K$1,F24<=L$1),I24,0)
M24=(MAX(0,MIN(J24,40-SUM($J$2:J23)))+(MAX(0,MIN(K24,40-SUM($K$2:K23)))+(MAX(0,MIN(L24,40-SUM($L$2:L23))))))
N24= 24*(H24-G24)
O24=TEXT(WEEKDAY(F24), "ddd")
J25=IF(F25<=J$1,I25,0)
K25=IF(AND(F25>J$1,F25<=K$1),I25,0)
L25=IF(AND(F25>K$1,F25<=L$1),I25,0)
M25=(MAX(0,MIN(J25,40-SUM($J$2:J24)))+(MAX(0,MIN(K25,40-SUM($K$2:K24)))+(MAX(0,MIN(L25,40-SUM($L$2:L24))))))
N25= 24*(H25-G25)
O25=TEXT(WEEKDAY(F25), "ddd")
J26=IF(F26<=J$1,I26,0)
K26=IF(AND(F26>J$1,F26<=K$1),I26,0)
L26=IF(AND(F26>K$1,F26<=L$1),I26,0)
M26=(MAX(0,MIN(J26,40-SUM($J$2:J25)))+(MAX(0,MIN(K26,40-SUM($K$2:K25)))+(MAX(0,MIN(L26,40-SUM($L$2:L25))))))
N26= 24*(H26-G26)
O26=TEXT(WEEKDAY(F26), "ddd")
O27=TEXT(WEEKDAY(F27), "ddd")
J27=IF(F27<=J$1,I27,0)
K27=IF(AND(F27>J$1,F27<=K$1),I27,0)
L27=IF(AND(F27>K$1,F27<=L$1),I27,0)
M27=(MAX(0,MIN(J27,40-SUM($J$2:J26)))+(MAX(0,MIN(K27,40-SUM($K$2:K26)))+(MAX(0,MIN(L27,40-SUM($L$2:L26))))))
J28=IF(F28<=J$1,I28,0)
K28=IF(AND(F28>J$1,F28<=K$1),I28,0)
L28=IF(AND(F28>K$1,F28<=L$1),I28,0)
M28=(MAX(0,MIN(J28,40-SUM($J$2:J27)))+(MAX(0,MIN(K28,40-SUM($K$2:K27)))+(MAX(0,MIN(L28,40-SUM($L$2:L27))))))
J29=IF(F29<=J$1,I29,0)
K29=IF(AND(F29>J$1,F29<=K$1),I29,0)
L29=IF(AND(F29>K$1,F29<=L$1),I29,0)
M29=(MAX(0,MIN(J29,40-SUM($J$2:J28)))+(MAX(0,MIN(K29,40-SUM($K$2:K28)))+(MAX(0,MIN(L29,40-SUM($L$2:L28))))))
J30=IF(F30<=J$1,I30,0)
K30=IF(AND(F30>J$1,F30<=K$1),I30,0)
L30=IF(AND(F30>K$1,F30<=L$1),I30,0)
M30=(MAX(0,MIN(J30,40-SUM($J$2:J29)))+(MAX(0,MIN(K30,40-SUM($K$2:K29)))+(MAX(0,MIN(L30,40-SUM($L$2:L29))))))
N28= 24*(H28-G28)
O28=TEXT(WEEKDAY(F28), "ddd")
N29= 24*(H29-G29)
O29=TEXT(WEEKDAY(F29), "ddd")
N30= 24*(H30-G30)
O30=TEXT(WEEKDAY(F30), "ddd")

<tbody>
</tbody>

<tbody>
</tbody>




Jim Wells
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Solved
Is there a better way to format table headers?
Replies
9
Views
238
shift-del
shift-del
S
  • Question
Formula for Equity Vesting
Replies
6
Views
479
Dave Patton
D
esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
584
esset
esset
HappyChappy
  • Solved
combining multiple cell Calculations
Replies
3
Views
162
Peter_SSs
Peter_SSs
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
138
ChetanPuri
C

Forum statistics

Threads
1,216,100
Messages
6,128,831
Members
449,471
Latest member
lachbee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top