Excel Formula help - please

[amityb]

I am trying to enter formula's on this bi-weekly time sheet that will only calculated regulars hours up to 40, and OT for all hours over 40. I can get it close, but just can't get it to work correctly. I'm hoping you all can help.

Thanks!

Date

Weekday

Dept# / Job#

Phase Code

Labor Code

Start Time

End Time

Length of Lunch

Total Hours Worked

Reg Hours

Over Time Hrs

VAC Paid

Sick Paid

VAC Unpd

Sick Unpd

Hol-iday

DAILY TOTAL HOURS

5/15/2017

Mon

8:00 AM

7:30 PM

0:30:00

11:00

0:00

5/16/2017

Tue

8:00 AM

7:30 PM

0:30:00

11:00

0:00

5/17/2017

Wed

8:00 AM

7:30 PM

0:30:00

11:00

0:00

5/18/2017

Thur

8:00 AM

7:30 PM

0:30:00

11:00

0:00

5/19/2017

Fri

8:00 AM

7:30 PM

0:30:00

11:00

0:00

5/20/2017

Sat

0:00

0:00

5/21/2017

Sun

0:00

0:00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

Is this what you're looking for?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Date	Weekday	Dept# / Job#	Phase Code	Labor Code	Start Time	End Time	Length of Lunch	Total Hours Worked	Reg Hours	Over Time Hrs	VAC Paid	Sick Paid	VAC Unpd	Sick Unpd	Hol-iday	DAILY TOTAL HOURS
2	5/15/2017	Mon				8:00 AM	7:30 PM	0:30:00	11:00	11	0						11:00
3	5/16/2017	Tue				8:00 AM	7:30 PM	0:30:00	11:00	11	0						11:00
4	5/17/2017	Wed				7:30 AM	7:30 PM	0:30:00	11:30	11.5	0						11:30
5	5/18/2017	Thur				8:00 AM	7:30 PM	0:30:00	11:00	6.5	4.5						11:00
6	5/19/2017	Fri				8:00 AM	7:30 PM	0:30:00	11:00	0	11						11:00
7	5/20/2017	Sat							0:00	0	0						0:00
8	5/21/2017	Sun							0:00	0	0						0:00

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

Sheet1​

Worksheet Formulas Cell Formula I2 =G2-F2-H2+IF(F2>G2,1,0) J2 =MEDIAN(40-SUM($I$1:$I1)*24,I2*24,0) K2 =I2*24-J2 Q2 =I2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[amityb]

Yes. Thank you!! Amity

Welcome to the forum.

Is this what you're looking for?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Date	Weekday	Dept# / Job#	Phase Code	Labor Code	Start Time	End Time	Length of Lunch	Total Hours Worked	Reg Hours	Over Time Hrs	VAC Paid	Sick Paid	VAC Unpd	Sick Unpd	Hol-iday	DAILY TOTAL HOURS
2	5/15/2017	Mon				8:00 AM	7:30 PM	0:30:00	11:00	11	0						11:00
3	5/16/2017	Tue				8:00 AM	7:30 PM	0:30:00	11:00	11	0						11:00
4	5/17/2017	Wed				7:30 AM	7:30 PM	0:30:00	11:30	11.5	0						11:30
5	5/18/2017	Thur				8:00 AM	7:30 PM	0:30:00	11:00	6.5	4.5						11:00
6	5/19/2017	Fri				8:00 AM	7:30 PM	0:30:00	11:00	0	11						11:00
7	5/20/2017	Sat							0:00	0	0						0:00
8	5/21/2017	Sun							0:00	0	0						0:00

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =G2-F2-H2+IF(F2>G2,1,0) J2 =MEDIAN(40-SUM($I$1:$I1)*24,I2*24,0) K2 =I2*24-J2 Q2 =I2 <tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

Glad to help!