Hello,
I am creating a spreadsheet to keep track of employees work times and then relate it to the amount of customers served to find productivity. The tricky part is that there are 3 time frames that we split the day between. We look at the amount of customers served during each time frame and divide it by total hours worked during each time frame to find the productivity for that period. It becomes tricky because some employee shifts cross over two time frames, so I want the hours worked during one time frame to sum up in one cell and the hours worked during the other time frame to sum up in another cell.
The spreadsheet is set up so that the employee start time is entered in a cell and the end time in a seperate cell. A third cell calculates the total hours worked. Now to explain the tricky part: The three time frames are from 12am-7am (Latenite), 7am-4pm (Day) and 4pm-12am (Swing). So if an employee works from 12pm-8pm I want 4hrs to calculate on another worksheet under the heading Day Hrs and 4 hrs under the heading Swing Hrs.The formula becomes a bit difficult when having to calculate if the hours worked within a time frame are at the start of a shift or at the end of a shift.
I have attached a mock spreadsheet. Any help would be greatly appreciated.