If/Then Problem for Time cards

[keross]

Hello Excel Geniuses!

I am in need of some help to get the brain juices flowing on this Monday morning. Here's the scoop:

I have created a time card calculator to calculate total time worked and distribute it to regular and overtime hours. Currently I have it set to calculate if over 8 hours then add the remaining as overtime, seems simple enough. This works for a day by day basis but I also need it to run against a running total of 40 hours per week. Meaning if more than 40 hours worked, then add all remaining entries as overtime.

I have tried creating an additional column to calculate the running total and then doing a simple if/then to return a 0 if under 40 or a 1 if over 40. But I am stuck in trying to do a nested If/then to check for the 40 hours before checking for the 8 hours. Can anyone PLEASE help point me in the right direction???

Many thanks!

[TABLE="width: 1826"]
<tbody>[TR]
[TD]Name:[/TD]
[TD="colspan: 9"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Log in[/TD]
[TD][/TD]
[TD]Lunch Starts[/TD]
[TD][/TD]
[TD]Lunch Ends[/TD]
[TD][/TD]
[TD]Log Out[/TD]
[TD][/TD]
[TD]Regular Hrs[/TD]
[TD][/TD]
[TD][/TD]
[TD]Overtime Hrs[/TD]
[TD]Sick Hours[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]0.248611111111111[/TD]
[TD]=MROUND(B4,15/(60*24))[/TD]
[TD]0.418055555555556[/TD]
[TD]=MROUND(D4,15/(60*24))[/TD]
[TD]0.438888888888889[/TD]
[TD]=MROUND(F4,15/(60*24))[/TD]
[TD]0.598611111111111[/TD]
[TD]=MROUND(H4,15/(60*24))[/TD]
[TD]=IF((((E4-C4)+(I4-G4))*24)>8,8,((E4-C4)+(I4-G4))*24)[/TD]
[TD]=J4[/TD]
[TD]=IF(K4>40,1,0)[/TD]
[TD]=IF(((E4-C4)+(I4-G4))*24>8, ((E4-C4)+(I4-G4))*24-8,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]0.166666666666667[/TD]
[TD]=MROUND(B5,15/(60*24))[/TD]
[TD]0.334722222222222[/TD]
[TD]=MROUND(D5,15/(60*24))[/TD]
[TD]0.357638888888889[/TD]
[TD]=MROUND(F5,15/(60*24))[/TD]
[TD]0.440972222222222[/TD]
[TD]=MROUND(H5,15/(60*24))[/TD]
[TD]=IF((((E5-C5)+(I5-G5))*24)>8,8,((E5-C5)+(I5-G5))*24)[/TD]
[TD]=K4+J5[/TD]
[TD]=IF(K5>40,1,0)[/TD]
[TD]=IF(((E5-C5)+(I5-G5))*24>8, ((E5-C5)+(I5-G5))*24-8,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]0.254166666666667[/TD]
[TD]=MROUND(B6,15/(60*24))[/TD]
[TD]0.419444444444444[/TD]
[TD]=MROUND(D6,15/(60*24))[/TD]
[TD]0.440277777777778[/TD]
[TD]=MROUND(F6,15/(60*24))[/TD]
[TD]0.602083333333333[/TD]
[TD]=MROUND(H6,15/(60*24))[/TD]
[TD]=IF((((E6-C6)+(I6-G6))*24)>8,8,((E6-C6)+(I6-G6))*24)[/TD]
[TD]=K5+J6[/TD]
[TD]=IF(K6>40,1,0)[/TD]
[TD]=IF(((E6-C6)+(I6-G6))*24>8, ((E6-C6)+(I6-G6))*24-8,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]0.254166666666667[/TD]
[TD]=MROUND(B7,15/(60*24))[/TD]
[TD]0.420138888888889[/TD]
[TD]=MROUND(D7,15/(60*24))[/TD]
[TD]0.440972222222222[/TD]
[TD]=MROUND(F7,15/(60*24))[/TD]
[TD]0.643055555555556[/TD]
[TD]=MROUND(H7,15/(60*24))[/TD]
[TD]=IF((((E7-C7)+(I7-G7))*24)>8,8,((E7-C7)+(I7-G7))*24)[/TD]
[TD]=K6+J7[/TD]
[TD]=IF(K7>40,1,0)[/TD]
[TD]=IF(((E7-C7)+(I7-G7))*24>8, ((E7-C7)+(I7-G7))*24-8,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]0.253472222222222[/TD]
[TD]=MROUND(B8,15/(60*24))[/TD]
[TD]0.418055555555556[/TD]
[TD]=MROUND(D8,15/(60*24))[/TD]
[TD]0.439583333333333[/TD]
[TD]=MROUND(F8,15/(60*24))[/TD]
[TD]0.645833333333333[/TD]
[TD]=MROUND(H8,15/(60*24))[/TD]
[TD]=IF((((E8-C8)+(I8-G8))*24)>8,8,((E8-C8)+(I8-G8))*24)[/TD]
[TD]=K7+J8[/TD]
[TD]=IF(K8>40,1,0)[/TD]
[TD]=IF(((E8-C8)+(I8-G8))*24>8, ((E8-C8)+(I8-G8))*24-8,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]0.215277777777778[/TD]
[TD]=MROUND(B9,15/(60*24))[/TD]
[TD][/TD]
[TD]=MROUND(D9,15/(60*24))[/TD]
[TD][/TD]
[TD]=MROUND(F9,15/(60*24))[/TD]
[TD]0.377083333333333[/TD]
[TD]=MROUND(H9,15/(60*24))[/TD]
[TD]=IF((((E9-C9)+(I9-G9))*24)>8,8,((E9-C9)+(I9-G9))*24)[/TD]
[TD]=K8+J9[/TD]
[TD]=IF(K9>40,1,0)[/TD]
[TD]=IF(((E9-C9)+(I9-G9))*24>8, ((E9-C9)+(I9-G9))*24-8,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD][/TD]
[TD]=MROUND(B10,15/(60*24))[/TD]
[TD][/TD]
[TD]=MROUND(D10,15/(60*24))[/TD]
[TD][/TD]
[TD]=MROUND(F10,15/(60*24))[/TD]
[TD][/TD]
[TD]=MROUND(H10,15/(60*24))[/TD]
[TD]=IF((((E10-C10)+(I10-G10))*24)>8,8,((E10-C10)+(I10-G10))*24)[/TD]
[TD]=K9+J10[/TD]
[TD]=IF(K10>40,1,0)[/TD]
[TD]=IF(((E10-C10)+(I10-G10))*24>8, ((E10-C10)+(I10-G10))*24-8,0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Hours[/TD]
[TD][/TD]
[TD]=SUM(J4:J10)[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUM(M4:M10)[/TD]
[TD]=SUM(N4:N10)*24[/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

Re: Calling All Excel Geniuses: A fun little If/Then Problem for Time cards

Maybe this:

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
daily OT	daily total (the long way, double check)
Mon
Tues
Wed
Thurs
Fri
(considering over 8 per day is OT)	considering 40 per week is regular time

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Actual Times[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Rounded times[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]daily reg[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]7:55[/TD]
[TD="align: center"]10:57[/TD]
[TD="align: center"]11:35[/TD]
[TD="align: center"]16:24[/TD]
[TD="align: right"][/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]11:00[/TD]
[TD="align: center"]11:30[/TD]
[TD="align: center"]16:30[/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]7:48[/TD]
[TD="align: center"]10:54[/TD]
[TD="align: center"]11:46[/TD]
[TD="align: center"]16:18[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7:45[/TD]
[TD="align: center"]11:00[/TD]
[TD="align: center"]11:45[/TD]
[TD="align: center"]16:15[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7.75[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]7.75[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]6:00[/TD]
[TD="align: center"]10:30[/TD]
[TD="align: center"]11:02[/TD]
[TD="align: center"]17:25[/TD]
[TD="align: right"][/TD]
[TD="align: center"]6:00[/TD]
[TD="align: center"]10:30[/TD]
[TD="align: center"]11:00[/TD]
[TD="align: center"]17:30[/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]7:30[/TD]
[TD="align: center"]11:01[/TD]
[TD="align: center"]11:34[/TD]
[TD="align: center"]16:30[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7:30[/TD]
[TD="align: center"]11:00[/TD]
[TD="align: center"]11:30[/TD]
[TD="align: center"]16:30[/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: right"]8.5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]5:00[/TD]
[TD="align: center"]11:03[/TD]
[TD="align: center"]11:36[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5:00[/TD]
[TD="align: center"]11:00[/TD]
[TD="align: center"]11:30[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]4.5[/TD]
[TD="align: right"]12.5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Weekly[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Regular Time[/TD]
[TD="align: right"]39.75[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Regular Time[/TD]
[TD="align: right"]40[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Overtime[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Overtime[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=MROUND(B2,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=MROUND(C2,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=MROUND(D2,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=MROUND(E2,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=MROUND(B3,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=MROUND(C3,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=MROUND(D3,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=MROUND(E3,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=MROUND(B4,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=MROUND(C4,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=MROUND(D4,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=MROUND(E4,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=MROUND(B5,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=MROUND(C5,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=MROUND(D5,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J5[/TH]
[TD="align: left"]=MROUND(E5,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=MROUND(B6,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6[/TH]
[TD="align: left"]=MROUND(C6,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]=MROUND(D6,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J6[/TH]
[TD="align: left"]=MROUND(E6,15/(60*24))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=IF(((J2-G2)-(I2-H2))*24>8,8,((J2-G2)-(I2-H2))*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=IF(((J2-G2)-(I2-H2))*24>8,(((J2-G2)-(I2-H2))*24)-8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2[/TH]
[TD="align: left"]=((J2-G2)-(I2-H2))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L3[/TH]
[TD="align: left"]=IF(((J3-G3)-(I3-H3))*24>8,8,((J3-G3)-(I3-H3))*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M3[/TH]
[TD="align: left"]=IF(((J3-G3)-(I3-H3))*24>8,(((J3-G3)-(I3-H3))*24)-8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N3[/TH]
[TD="align: left"]=((J3-G3)-(I3-H3))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L4[/TH]
[TD="align: left"]=IF(((J4-G4)-(I4-H4))*24>8,8,((J4-G4)-(I4-H4))*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M4[/TH]
[TD="align: left"]=IF(((J4-G4)-(I4-H4))*24>8,(((J4-G4)-(I4-H4))*24)-8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N4[/TH]
[TD="align: left"]=((J4-G4)-(I4-H4))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L5[/TH]
[TD="align: left"]=IF(((J5-G5)-(I5-H5))*24>8,8,((J5-G5)-(I5-H5))*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M5[/TH]
[TD="align: left"]=IF(((J5-G5)-(I5-H5))*24>8,(((J5-G5)-(I5-H5))*24)-8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N5[/TH]
[TD="align: left"]=((J5-G5)-(I5-H5))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L6[/TH]
[TD="align: left"]=IF(((J6-G6)-(I6-H6))*24>8,8,((J6-G6)-(I6-H6))*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M6[/TH]
[TD="align: left"]=IF(((J6-G6)-(I6-H6))*24>8,(((J6-G6)-(I6-H6))*24)-8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N6[/TH]
[TD="align: left"]=((J6-G6)-(I6-H6))*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=SUM(L2:L6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=SUM(M2:M6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I9[/TH]
[TD="align: left"]=IF(SUM(L2:L6,M2:M6)>40,40,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I10[/TH]
[TD="align: left"]=SUM(L2:L6,M2:M6)-40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Does that do what you want?

 

[keross]

Re: Calling All Excel Geniuses: A fun little If/Then Problem for Time cards

Thank you so much for your help J! This is ALMOST there, the only other thing I'd like it to do is to automatically move any Overtime after 40 hours into the Overtime hours column in the L & M columns. Your solution calculated the right totals, but I'd also like it to display them in the appropriate columns. For example, I used the following numbers and on Saturday the 3.75 hours should not all be in the Reg hours but rather 2.25 in Reg and 1.5 in Overtime. Does this make sense? Thank you again for your help!!!

[TABLE="width: 992"]
<colgroup><col><col><col span="5"><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Log in [/TD]
[TD]Lunch Starts[/TD]
[TD]Lunch Ends[/TD]
[TD]Log Out[/TD]
[TD] [/TD]
[TD]Rounded Lunch[/TD]
[TD]Rounded Lunch[/TD]
[TD]Rounded Out[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Regular Hrs[/TD]
[TD]Overtime Hrs[/TD]
[TD]Daily Total[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]5:58 AM[/TD]
[TD="align: right"]10:02 AM[/TD]
[TD="align: right"]10:32 AM[/TD]
[TD="align: right"]2:22 PM[/TD]
[TD] [/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"]2:15 PM[/TD]
[TD] [/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]7.75[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]8:02 AM[/TD]
[TD="align: right"]8:35 AM[/TD]
[TD="align: right"]10:35 AM[/TD]
[TD] [/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]8:30 AM[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD] [/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]6.00[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD="align: right"]6:06 AM[/TD]
[TD="align: right"]10:04 AM[/TD]
[TD="align: right"]10:34 AM[/TD]
[TD="align: right"]2:27 PM[/TD]
[TD] [/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"]2:30 PM[/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]8.00[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD="align: right"]6:06 AM[/TD]
[TD="align: right"]10:05 AM[/TD]
[TD="align: right"]10:35 AM[/TD]
[TD="align: right"]3:26 PM[/TD]
[TD] [/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]9.00[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"]6:05 AM[/TD]
[TD="align: right"]10:02 AM[/TD]
[TD="align: right"]10:33 AM[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD] [/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]9.00[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD="align: right"]5:10 AM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9:03 AM[/TD]
[TD] [/TD]
[TD="align: right"]5:15 AM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD] [/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]3.75[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Weekly[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Regular Time[/TD]
[TD="align: right"]41.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Regular Time[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Overtime[/TD]
[TD="align: right"]2.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Overtime[/TD]
[TD="align: right"]3.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 928"]
<colgroup><col><col span="5"><col><col span="4"><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 300"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]