I need an Excel function that can calculate a worker's total hours for a day.
For each day, there are a series of two or four times.
If there are two times, it is a "shift start" and "shift end" for a shift.
If there are four times, there is a "shift start", "lunch start", "lunch end", "shift end".
I need a column to show the "lunch time" (if the employee clocked out for lunch), and then the total shift time (the difference between the "shift start" and "shift end", minus the "lunch time"). The result should be in decimal format ("7.5" hours instead of "7:30" hours)
Some of the shifts extend past midnight, so it will also need to account for overnight calculations.
The columns are set up this way:
| A | B | C | D | E | F |
| Date | Name | Shift Start (IN) | Lunch Start(Out) | Lunch End (IN) | Shift End (OUT) |
I've put a screen grab of the spreadsheet here:
[url removed, login to view]