deducting varying breaks

[vonvon87]

I have used excel to create a rota for my coleagues, i have hour in and out and used aformula to work out the overall amount of hours in the day. but if they work 8 hours they get 30 mins unapid break , and between 4 and half to 5 hours, 15 mins and 6-7.5 hours a 20 minute break which i would need to deuct from the total hours. is there a way to do this

Thanks

[FlameRetired]

Hello vonvon87. Welcome to the forum.

Without a sample file I am left to guess what the layout of data vs output looks like. So try this.

With lookup table for time ranges in F2:G4 your formula in D2 and copied down.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1	In	Out	Worked	Net		Worked	Deduct
2	8:00:00	10:00:00	2:00:00	2:00:00		0	0
3	8:00:00	12:30:00	4:30:00	4:15:00		4:30:00	0:15:00
4	8:00:00	14:00:00	6:00:00	5:40:00		6:00:00	0:20:00
5	8:00:00	16:00:00	8:00:00	7:30:00		8:00:00	0:30:00