Overtime work sheet

[RahulNa]

Dear Friends,

I need a overtime sheet in which if a guy take less lunch brake it should not be deducted from his total work time.

I am attaching a excel sheet request you to please assist me with it.

http://www.calculatehours.com/excel/64OvertimeMonthlyCondensed.xls

 

[Robert Mika]

Dear Friends,

I need a overtime sheet in which if a guy take less lunch brake it should not be deducted from his total work time.

I am attaching a excel sheet request you to please assist me with it.

http://www.calculatehours.com/excel/64OvertimeMonthlyCondensed.xls

Can you show desired output?

 

[RahulNa]

Dear Robert,

The below is the format of the excel, the issue which i am facing now is, if a guy comes late or have Lunch time exceeding limit or doesn't take the lunch, how can i have this variable calculated in the hours worked so that the total working hours be calculated.

<colgroup><col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" span="6" width="70"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> </colgroup><tbody>
[TD="class: xl65, width: 126"]Week of 06/1/09[/TD]
[TD="class: xl66, width: 70"]Mon[/TD]
[TD="class: xl66, width: 70"]Tues[/TD]
[TD="class: xl66, width: 70"]Wed[/TD]
[TD="class: xl66, width: 70"]Thurs[/TD]
[TD="class: xl66, width: 70"]Fri[/TD]
[TD="class: xl66, width: 70"]Sat[/TD]
[TD="class: xl66, width: 88"]Sun[/TD]

[TD="class: xl67"]Log In[/TD]
[TD="class: xl68"]9:00 AM[/TD]
[TD="class: xl68"]9:15 AM[/TD]
[TD="class: xl68"]8:50 AM[/TD]
[TD="class: xl68"]9:20 AM[/TD]
[TD="class: xl68"]11:10 AM[/TD]
[TD="class: xl68"]12:15 PM[/TD]
[TD="class: xl69"] [/TD]

[TD="class: xl67"]Lunch Starts[/TD]
[TD="class: xl70"]12:00 PM[/TD]
[TD="class: xl70"]12:00 PM[/TD]
[TD="class: xl70"]12:10 PM[/TD]
[TD="class: xl70"]12:00 PM[/TD]
[TD="class: xl70"]12:15 PM[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"] [/TD]

[TD="class: xl67"]Lunch Ends[/TD]
[TD="class: xl70"]12:30 PM[/TD]
[TD="class: xl70"]12:15 PM[/TD]
[TD="class: xl70"]12:30 PM[/TD]
[TD="class: xl70"]12:30 PM[/TD]
[TD="class: xl70"]12:30 PM[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"] [/TD]

[TD="class: xl67"]Log Out[/TD]
[TD="class: xl70"]7:00 PM[/TD]
[TD="class: xl70"]6:00 PM[/TD]
[TD="class: xl70"]5:00 PM[/TD]
[TD="class: xl70"]5:30 PM[/TD]
[TD="class: xl70"]5:00 PM[/TD]
[TD="class: xl70"]4:00 PM[/TD]
[TD="class: xl71"] [/TD]

[TD="class: xl72"]Hours Worked[/TD]
[TD="class: xl73"]9.50[/TD]
[TD="class: xl73"]8.50[/TD]
[TD="class: xl73"]7.83[/TD]
[TD="class: xl73"]7.67[/TD]
[TD="class: xl73"]5.58[/TD]
[TD="class: xl73"]3.75[/TD]
[TD="class: xl73"]0.00[/TD]

[TD="class: xl64"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl75"]Reg. Hrs.[/TD]
[TD="class: xl76"]40.00[/TD]
[TD="class: xl75"]Over. Hrs.[/TD]
[TD="class: xl76"]2.83[/TD]
[TD="class: xl75"]Total Hrs.[/TD]
[TD="class: xl76"]42.83[/TD]

</tbody>

 

[Akashwani]

Hi,

I'm sure Robert has a solution for you, but here's my attempt.....
I have added a small table to help with the calculations.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Week of 06/1/09	Mon	Tues	Wed	Thurs	Fri	Sat	Sun		Start Time	End time	Lunch	Work Day	Reg Hrs Total
2	Log In	9:00 AM	9:15 AM	8:50 AM	9:20 AM	11:10 AM	12:15 PM			9:00 AM	5:00 PM	00:30	8	40
3	Lunch Starts	12:00 PM	12:00 PM	12:10 PM	12:00 PM	12:15 PM
4	Lunch Ends	12:30 PM	12:15 PM	12:30 PM	12:30 PM	12:30 PM
5	Log Out	7:00 PM	6:00 PM	5:00 PM	5:30 PM	5:00 PM	4:00 PM
6	Hours Worked	9:30	8:30	7:50	7:40	5:35	3:45	0:00
7	Reg. Hrs.	8:00	8:00	7:50	7:40	5:35	3:45	0:00
8	Over. Hrs	1:30	0:30
9	Reg. Hrs.	40	Over. Hrs.	02:50	Total Hrs.	42:50
10
Sheet6

The formulas in B6, B7, B8 need to be copied across.

The results show in Hours and Minutes, I'm unsure how to get it as a decimal, sorry.

I hope this helps.

Ak

 

[miss_ell]

Hello,

Assuming that the table starts from A1, the formulas I've worked out for daily hours worked are:

B8 (Mon) =(B3-B2)+(B5-B4)

then copy across to Sunday (H8).

Total hours (H13) =SUM(B8:H8) - 42:50.
Regular Hours will be constant at 40.
Overtime hours =H13-D13 - 2:50.

All cells are formatted [h]:mm - hours and minutes.

 

[RahulNa]

Thanks Akashwani for the suggestion.

I have one query the formula which u mentioned

B6=(B5-B2)-(B4-B3)

If i dont take any lunch how is it possible that the half hour gets added automatically is it possible.

 

[Akashwani]

Hi,

Does this work?...

=IF(B4<>"",(B5-B2)-(B4-B3),(B5-B2)-(B4-B3)-$L$2)

Ak

 

[Akashwani]

Hi,

Oops, I deducted the 30 minutes!!

Use this to counter none working days....

=IF(B4<>"",(B5-B2)-(B4-B3),IF(B5<>"",(B5-B2)-(B4-B3)+$L$2,""))

Ak

 

[RahulNa]

Dear AK,

I Would request you to please do the calculation according to the below table :-

[TABLE="width: 465"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Week of 06/1/09[/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]Log In[/TD]
[TD]9:00 AM[/TD]
[TD]9:15 AM[/TD]
[TD]8:50 AM[/TD]
[TD]9:20 AM[/TD]
[TD]11:10 AM[/TD]
[TD]12:15 PM[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Lunch Starts[/TD]
[TD]12:00 PM[/TD]
[TD]12:00 PM[/TD]
[TD]12:10 PM[/TD]
[TD]12:00 PM[/TD]
[TD]12:15 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Lunch Ends[/TD]
[TD]12:30 PM[/TD]
[TD]12:15 PM[/TD]
[TD]12:30 PM[/TD]
[TD]12:30 PM[/TD]
[TD]12:30 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Log Out[/TD]
[TD]7:00 PM[/TD]
[TD]6:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]5:30 PM[/TD]
[TD]5:00 PM[/TD]
[TD]4:00 PM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hours Worked[/TD]
[TD]9.50[/TD]
[TD]8.50[/TD]
[TD]7.83[/TD]
[TD]7.67[/TD]
[TD]5.58[/TD]
[TD]3.75[/TD]
[TD]0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akashwani]

Hi,

I'm sorry but I don't have the skills to do that, that is why I gave the formulas and the layout that I did.

Ak