How to Pivot/Formula to audit for payroll Breaktime

[mizzmeeshelly]

Hi all,

In my state, employee has to take a 10 minute break every 3.5 hours worked consecutively and a lunch break every 6 hours of work.

Does any excel master out there know of a way I can audit to make sure the breaks are added on their work schedule with the information below?

Below is only an example, we have more than 100 employees with payroll running bi-weekly so we are trying to avoid looking through each employee's calendar.

DateWorked	DateTimeFrom	DateTimeTo	EmployeeName	WorkCode	HoursWorked	Rate
9/16/2020	9/16/20 9:00 AM	9/16/20 1:30 PM	Apple	Admin Work	3.5	30
9/16/2020	9/16/20 1:30 PM	9/16/20 1:40 PM	Apple	Breaks	0.16	15
9/16/2020	9/16/20 1:40 PM	9/16/20 2:40 PM	Apple	Admin Work	1	30
9/16/2020	9/16/20 2:40 PM	9/16/20 3:40 PM	Apple	Lunch	1	0
9/16/2020	9/16/20 3:40 PM	9/16/20 4:40 PM	Apple	Meeting	1	30
9/16/2020	9/16/20 4:40 PM	9/16/20 5:40 PM	Apple	Admin Work	1	30
9/16/2020	9/16/20 9:00 AM	9/16/20 1:30 PM	Banana	Admin Work	3.5	35
9/16/2020	9/16/20 1:30 PM	9/16/20 1:40 PM	Banana	Breaks	0.16	15
9/16/2020	9/16/20 1:40 PM	9/16/20 2:40 PM	Banana	Admin Work	1	35
9/16/2020	9/16/20 2:40 PM	9/16/20 3:40 PM	Banana	Lunch	1	0
9/16/2020	9/16/20 3:40 PM	9/16/20 5:40 PM	Banana	Admin Work	2	35
9/16/2020	9/16/20 5:40 PM	9/16/20 5:50 PM	Banana	Breaks	0.16	15
9/16/2020	9/16/20 5:50 PM	9/16/20 7:20 PM	Banana	Admin Work	1.5	35

Thanks in advance!

 

[mizzmeeshelly]

Any help with this is much appreciated!