Auto deduct break from total working hours

[Fayyfatt]

Hi,

Can anyone please help me.

I want to auto deduct break from total working hours base on several situation.

1) Monday - Friday
- 0.5 for =>3hours
- 1.0 for =>6hours

2) Saturday - Sunday
- 0.5 for =>6hours
- 1.0 for =>8hours

3) Public Holiday
- 0.5 for =>6hours
- 1.0 for =>8hours

 

[Ramballah]

I'm sorry but this post is too vague for me. Can you please explain what you want in more detail? What is this information you have given us? Can you give an explanation of that because of how i see it. for every 3h u work u get 30min break for monday till friday and for saturday and sunday its 30min break if u work 6h and 1 hour for 8h shift? Then if this is the case where do you calculate your total work hours? and how do you calculate them.

 

[steve the fish]

With a date in A1, the hours worked in B1 and a named range called 'holidays' with the public holiday dates in it:

=IF(OR(ISNUMBER(MATCH(A1,holidays,0)),WEEKDAY(A1,2)>5),B1-LOOKUP(B1,{0,6,8},{0,0.5,1}),B1-LOOKUP(B1,{0,3,6},{0,0.5,1}))

 

[oldbrewer]

[TABLE="width: 1180"]
<colgroup><col span="2"><col span="3"><col span="12"><col></colgroup><tbody>[TR]
[TD="colspan: 9"]I want to auto deduct break from total working hours base on several situation.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col R[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]1) Monday - Friday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bigtable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]- 0.5 for =>3hours[/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][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]- 1.0 for =>6hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]monfri[/TD]
[TD="colspan: 2"]satsunpubhol[/TD]
[TD][/TD]
[TD]pubhol[/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="align: right"]0.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2018[/TD]
[/TR]
[TR]
[TD="colspan: 3"]2) Saturday - Sunday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/01/2018[/TD]
[/TR]
[TR]
[TD="colspan: 2"]- 0.5 for =>6hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/06/2018[/TD]
[/TR]
[TR]
[TD="colspan: 3"]- 1.0 for =>8hours [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/12/2018[/TD]
[/TR]
[TR]
[TD="colspan: 2"]3) Public Holiday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/12/2018[/TD]
[/TR]
[TR]
[TD="colspan: 2"]- 0.5 for =>6hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]- 1.0 for =>8hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.5[/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="align: right"]8.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/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="align: right"]9.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/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="align: right"]10.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/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="align: right"]11.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name[/TD]
[TD]date[/TD]
[TD]satsun[/TD]
[TD]pubhol=1[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]hours[/TD]
[TD]break[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]alan[/TD]
[TD="align: right"]07/01/2018[/TD]
[TD]Sun[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]0.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]bill[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD]Thu[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]6.50[/TD]
[TD="align: right"]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]colin[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD]Tue[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dave[/TD]
[TD="align: right"]29/12/2017[/TD]
[TD]Fri[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ed[/TD]
[TD="align: right"]06/01/2018[/TD]
[TD]Sat[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]fred[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD]Fri[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18.00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]george[/TD]
[TD="align: right"]06/01/2018[/TD]
[TD]Sat[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]0.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]harry[/TD]
[TD="align: right"]06/01/2018[/TD]
[TD]Sat[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ian[/TD]
[TD="align: right"]14/01/2018[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]daytable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]joe[/TD]
[TD="align: right"]14/01/2018[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]0.50[/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fri[/TD]
[TD="align: right"]2[/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]mon[/TD]
[TD="align: right"]2[/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]sat[/TD]
[TD="align: right"]3[/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]Sun[/TD]
[TD="align: right"]3[/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]thu[/TD]
[TD="align: right"]2[/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]tue[/TD]
[TD="align: right"]2[/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]wed[/TD]
[TD="align: right"]2[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fayyfatt]

can you show me in excel?