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]

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

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

 

[Fayyfatt]

can you show me in excel?