Dividing worked shift into one hour time slots

[deerxing]

I am trying to divde a work shift up into buckets of 1 hour. If an employee clocks into work at 9am and out at 4:15pm, I would like to divide the shifts into 1 hour time slots using a formula (ex. 9am - 10am 1hour, 10am to 11am 1hour and so on. The last 15 minutes of the shift should be represented in the 4pm to 5pm bucket. I am curently using this formula =MAX(MIN(DF3,$I3)-MAX(DE3,$H3),0). It works great except it does not work on shifts ending at midnight, nor does it parce the 15min, 30min or 45min at the end of the shift into the proper bucket.

Any assistance you can provide is appreciated. Thankyou

 

[jasonb75]

Duplicate thread

http://www.mrexcel.com/forum/showthread.php?t=540333