Calculating hours worked

[mahmed1]

Hi Guys,

I need a formula that will Calculate days and hours worked but need to take certain criterias into account. Say i have dates and time worked and finished liked this

<TABLE style="WIDTH: 165pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=220><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 82pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=109 align=right>22/03/2011 08:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=111 align=right>22/03/2011 16:30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 align=right>
23/03/2011 08:00
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>
23/03/2011 17:00
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 align=right>
24/03/2011 08:30
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>
24/03/2011 17:30
</TD></TR></TBODY></TABLE>

Normal working hours are between 08:30 - 16:30 plus 30 mins lunch if in a day you have worked more than 6 hours. If you worked less than 6 hours then you have no lunch.
1 day is 7.5 hours

What i need to take into account is exclude weekends. Take the lunch criteria above into account. In 1 cell give me the days and hours worked between the normal working hours and in another cell give me the days and hours worked outside the rostered hours.

 

[CDNWolf]

Could you please post a sample spreadsheet in order to view cell layot please...

 

[mahmed1]

Could you please post a sample spreadsheet in order to view cell layot please...

How can i post a sample spreadsheet?

Would it help if i said that i had date and time started in cell a2 copied down and date and time finished in cell b2 copied down

I have normal business working start time in cell c2 and normal business finish time in d2.

I would like the results in cell e2 for the total days and hours worked in business hours and anything out of business hours in f2

hth

 

[mahmed1]

Bump

 

[mahmed1]

Bump

Anyone help please

 

[mahmed1]

Bump