John Smith wrote:
I need to analyze the hours worked by employees on certain days off to
determine how many hours, during what would be their normal shift time,
that they actually worked. Here's how I have the columns: A= Time
started, B= time finished, C= the total hours worked, D= normal shift
time start, E= normal shift end time. If someone works from 3:00 AM
(3:00) until 9:00 AM (9:00), leaves but is recalled at 2:00 PM (14:00)
until 5:00 PM (17:00), and the normal shift time is 7:00 AM (7:00) until
3:PM (15:00), F= what formula will capture the hours worked between 7:00
AM and 3:00 PM? I would likely have the room to capture such split
times worked in another column if need be.


Hello John,
Here is how you would set it up. I hope this helps....

A = 9:00 AM Start Time
B = 5:45 PM End Time
C = '=(B2<A2)+B2-A2' Hours Worked
D =
'=IF(B2=A2,MAX(0,MIN(B2,"16:30")-MAX(A2,"8:00")),MAX(0,"16:30"-MAX(A2,"8:00"))+MAX(0,MIN(B2,"16:30")-"8:00"))'
(Hours Worked or in this case (CORE HOURS=8:00 - 4:30 PM)
E = '=(B2<A2)+B2-A2 - D2' (Prime time hours or hours worked outside
core hours.)

Try that..mess around with the formula to make it work for you and let
me know the results.

Have a great day!
Jenni