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!

Name:

Date

Log in

Lunch Starts

Lunch Ends

Log Out

Regular Hrs

Overtime Hrs

Sick Hours

Monday

0.248611111111111

=MROUND(B4,15/(60*24))

0.418055555555556

=MROUND(D4,15/(60*24))

0.438888888888889

=MROUND(F4,15/(60*24))

0.598611111111111

=MROUND(H4,15/(60*24))

=IF((((E4-C4)+(I4-G4))*24)>8,8,((E4-C4)+(I4-G4))*24)

=J4

=IF(K4>40,1,0)

=IF(((E4-C4)+(I4-G4))*24>8, ((E4-C4)+(I4-G4))*24-8,0)

Tuesday

0.166666666666667

=MROUND(B5,15/(60*24))

0.334722222222222

=MROUND(D5,15/(60*24))

0.357638888888889

=MROUND(F5,15/(60*24))

0.440972222222222

=MROUND(H5,15/(60*24))

=IF((((E5-C5)+(I5-G5))*24)>8,8,((E5-C5)+(I5-G5))*24)

=K4+J5

=IF(K5>40,1,0)

=IF(((E5-C5)+(I5-G5))*24>8, ((E5-C5)+(I5-G5))*24-8,0)

Wednesday

0.254166666666667

=MROUND(B6,15/(60*24))

0.419444444444444

=MROUND(D6,15/(60*24))

0.440277777777778

=MROUND(F6,15/(60*24))

0.602083333333333

=MROUND(H6,15/(60*24))

=IF((((E6-C6)+(I6-G6))*24)>8,8,((E6-C6)+(I6-G6))*24)

=K5+J6

=IF(K6>40,1,0)

=IF(((E6-C6)+(I6-G6))*24>8, ((E6-C6)+(I6-G6))*24-8,0)

Thursday

0.254166666666667

=MROUND(B7,15/(60*24))

0.420138888888889

=MROUND(D7,15/(60*24))

0.440972222222222

=MROUND(F7,15/(60*24))

0.643055555555556

=MROUND(H7,15/(60*24))

=IF((((E7-C7)+(I7-G7))*24)>8,8,((E7-C7)+(I7-G7))*24)

=K6+J7

=IF(K7>40,1,0)

=IF(((E7-C7)+(I7-G7))*24>8, ((E7-C7)+(I7-G7))*24-8,0)

Friday

0.253472222222222

=MROUND(B8,15/(60*24))

0.418055555555556

=MROUND(D8,15/(60*24))

0.439583333333333

=MROUND(F8,15/(60*24))

0.645833333333333

=MROUND(H8,15/(60*24))

=IF((((E8-C8)+(I8-G8))*24)>8,8,((E8-C8)+(I8-G8))*24)

=K7+J8

=IF(K8>40,1,0)

=IF(((E8-C8)+(I8-G8))*24>8, ((E8-C8)+(I8-G8))*24-8,0)

Saturday

0.215277777777778

=MROUND(B9,15/(60*24))

=MROUND(D9,15/(60*24))

=MROUND(F9,15/(60*24))

0.377083333333333

=MROUND(H9,15/(60*24))

=IF((((E9-C9)+(I9-G9))*24)>8,8,((E9-C9)+(I9-G9))*24)

=K8+J9

=IF(K9>40,1,0)

=IF(((E9-C9)+(I9-G9))*24>8, ((E9-C9)+(I9-G9))*24-8,0)

Sunday

=MROUND(B10,15/(60*24))

=MROUND(D10,15/(60*24))

=MROUND(F10,15/(60*24))

=MROUND(H10,15/(60*24))

=IF((((E10-C10)+(I10-G10))*24)>8,8,((E10-C10)+(I10-G10))*24)

=K9+J10

=IF(K10>40,1,0)

=IF(((E10-C10)+(I10-G10))*24>8, ((E10-C10)+(I10-G10))*24-8,0)

Total Hours

=SUM(J4:J10)

=SUM(M4:M10)

=SUM(N4:N10)*24

<tbody>
</tbody>

 

[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
1		Actual Times					Rounded times					daily reg	daily OT	daily total (the long way, double check)
2	Mon	7:55	10:57	11:35	16:24		8:00	11:00	11:30	16:30		8	0	8
3	Tues	7:48	10:54	11:46	16:18		7:45	11:00	11:45	16:15		7.75	0	7.75
4	Wed	6:00	10:30	11:02	17:25		6:00	10:30	11:00	17:30		8	3	11
5	Thurs	7:30	11:01	11:34	16:30		7:30	11:00	11:30	16:30		8	0.5	8.5
6	Fri	5:00	11:03	11:36	18:00		5:00	11:00	11:30	18:00		8	4.5	12.5
7
8		Weekly
9		Regular Time	39.75	(considering over 8 per day is OT)				Regular Time	40	considering 40 per week is regular time
10		Overtime	8					Overtime	7.75

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =MROUND(B2,15/(60*24)) H2 =MROUND(C2,15/(60*24)) I2 =MROUND(D2,15/(60*24)) J2 =MROUND(E2,15/(60*24)) G3 =MROUND(B3,15/(60*24)) H3 =MROUND(C3,15/(60*24)) I3 =MROUND(D3,15/(60*24)) J3 =MROUND(E3,15/(60*24)) G4 =MROUND(B4,15/(60*24)) H4 =MROUND(C4,15/(60*24)) I4 =MROUND(D4,15/(60*24)) J4 =MROUND(E4,15/(60*24)) G5 =MROUND(B5,15/(60*24)) H5 =MROUND(C5,15/(60*24)) I5 =MROUND(D5,15/(60*24)) J5 =MROUND(E5,15/(60*24)) G6 =MROUND(B6,15/(60*24)) H6 =MROUND(C6,15/(60*24)) I6 =MROUND(D6,15/(60*24)) J6 =MROUND(E6,15/(60*24)) L2 =IF(((J2-G2)-(I2-H2))*24>8,8,((J2-G2)-(I2-H2))*24) M2 =IF(((J2-G2)-(I2-H2))*24>8,(((J2-G2)-(I2-H2))*24)-8,0) N2 =((J2-G2)-(I2-H2))*24 L3 =IF(((J3-G3)-(I3-H3))*24>8,8,((J3-G3)-(I3-H3))*24) M3 =IF(((J3-G3)-(I3-H3))*24>8,(((J3-G3)-(I3-H3))*24)-8,0) N3 =((J3-G3)-(I3-H3))*24 L4 =IF(((J4-G4)-(I4-H4))*24>8,8,((J4-G4)-(I4-H4))*24) M4 =IF(((J4-G4)-(I4-H4))*24>8,(((J4-G4)-(I4-H4))*24)-8,0) N4 =((J4-G4)-(I4-H4))*24 L5 =IF(((J5-G5)-(I5-H5))*24>8,8,((J5-G5)-(I5-H5))*24) M5 =IF(((J5-G5)-(I5-H5))*24>8,(((J5-G5)-(I5-H5))*24)-8,0) N5 =((J5-G5)-(I5-H5))*24 L6 =IF(((J6-G6)-(I6-H6))*24>8,8,((J6-G6)-(I6-H6))*24) M6 =IF(((J6-G6)-(I6-H6))*24>8,(((J6-G6)-(I6-H6))*24)-8,0) N6 =((J6-G6)-(I6-H6))*24 C9 =SUM(L2:L6) C10 =SUM(M2:M6) I9 =IF(SUM(L2:L6,M2:M6)>40,40,"") I10 =SUM(L2:L6,M2:M6)-40 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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!!!

Date

Log in

Lunch Starts

Lunch Ends

Log Out

Rounded Lunch

Rounded Lunch

Rounded Out

Regular Hrs

Overtime Hrs

Daily Total

Monday

5:58 AM

10:02 AM

10:32 AM

2:22 PM

6:00 AM

10:00 AM

10:30 AM

2:15 PM

7.75

0.00

7.75

Tuesday

4:00 AM

8:02 AM

8:35 AM

10:35 AM

4:00 AM

8:00 AM

8:30 AM

10:30 AM

6

0.00

6.00

Wednesday

6:06 AM

10:04 AM

10:34 AM

2:27 PM

6:00 AM

10:00 AM

10:30 AM

2:30 PM

8

0.00

8.00

Thursday

6:06 AM

10:05 AM

10:35 AM

3:26 PM

6:00 AM

10:00 AM

10:30 AM

3:30 PM

8

1.00

9.00

Friday

6:05 AM

10:02 AM

10:33 AM

3:30 PM

6:00 AM

10:00 AM

10:30 AM

3:30 PM

8

1.00

9.00

Saturday

5:10 AM

9:03 AM

5:15 AM

12:00 AM

12:00 AM

9:00 AM

3.75

0.00

3.75

Sunday

12:00 AM

12:00 AM

12:00 AM

12:00 AM

0

0.00

0.00

Weekly

Regular Time

41.5

Regular Time

40

Overtime

2.00

Overtime

3.50

<colgroup><col><col><col span="5"><col><col span="4"><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col span="5"><col><col span="4"><col><col></colgroup><tbody></tbody>

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>