Calculating daily overtime and weekly overtime

We use a time clock built in to my LOB software. It does not calculate overtime so when I do payroll I have to go through and look for any days over 8 hours or any weeks over 40 hours for an employee. I can copy and paste the time clock data into Excel so I'm trying to figure out a way to calculate overtime both daily (more than 8 hours in one day) and weekly (more than 40 hours in a week). I've attached an example of what the data looks like when pasted into Excel.

One of the issues is that on some days an employee may clock in only twice and on other days, when they take lunch, they would have four clock-in/clock-out events. What I would like to do is to have a visual indicator (eg, color the cell red) on any day where there is more than 8 hours worked or any week where there is more than 40 hours worked. Any suggestions for a starting point on this?

Thanks!

Hi Guy!

You can try the pivot table as the starting point to simplify the data you have and you need to set-up the formulas and formatting in your own preference.