Break Lunch Adherence

[James14]

Hey Guys, Need a bit of help again, I need to create a report that will look at the time that a group of people are logged in and then based off of how long they were staffed, tell me how many minutes they should be in break and lunch.

Ex.

If an employee is logged in for 2Hrs 10min they get a 10min break
If an employee is logged in for 4-5 Hrs they get a 15 min break
If an employee is logged in for 5.5-6.5 hours they get a 15min break and 30 min lunch
If an employee is logged in for 7.0 -11.5 hours two 15min breaks and one 30 min lunch
If an employee is logged in for 12 hours three 15min breaks, plus one 30 lunch.

the report I have pulled shows name and staffed time.

I have flirted with the Idea of a macro and assigning variables... and a nested if statement... but not quite sure on how to get there... Hope there is help to get on this..

Thanks guys

 

[GlennUK]

How about a lookup table:

Excel Workbook
	A	B	C	D	E
1	Worked Hours	Calculated break		Lookup table of breaks
2	01:00	No break		0	No break
3	02:00	No break		02:10	10mins
4	03:00	10mins		04:00	15 min
5	03:59	10mins		05:30	15min+30 min lunch
6	04:00	15 min		07:00	two 15min breaks and one 30 min lunch
7	05:29	15 min		12:00	three 15min breaks and one 30 min lunch
8	05:30	15min+30 min lunch
9	06:59	15min+30 min lunch
10	07:00	two 15min breaks and one 30 min lunch
11	11:30	two 15min breaks and one 30 min lunch
12	11:59	two 15min breaks and one 30 min lunch
13	12:00	three 15min breaks and one 30 min lunch
Sheet13

 

[James14]

I think thats it, it seems far too simple to have worked... But its returning exactly what I need... Could I have been overthinking this? You rock! lol thanks again

 

[GlennUK]

My pleasure!