Dividing Time Into Buckets

[deerxing]

I am trying to account for all hours punched on a time clock in 1 hour time slots. My approach is to use this formula =MAX(MIN(DF52,$I52)-MAX(DE52,$H52),0). DF = 0 (or 12:00AM), I = Employee Out Punch - DE = 23:00 (or 11:00 PM), H = Employee In Punch, to determine if any portion of the shift falls between 11pm and midnight. In this example any hours worked in a shift between 11:00pm and 12:00am should show up in the designated column. In general this formula works great with 2 exceptions. (1) Any increments of time that are less than 1 hour do not show up in the designated time slot bucket (example, 8am - 9:45am the 45 minutes does not populate in the 9am to 10am bucket, the rest of the shift appears accurately). (2) The above formula does not for any shift ending at midnight, 0 (or 12:00AM).

Any assistance is appreciated.

 

[jasonb75]

Duplicate thread.

http://www.mrexcel.com/forum/showthread.php?p=2668683#post2668683

It's hard to make out exactly what you're trying to achieve. Please provide an example of the expected result.

 

[deerxing]

If I have a worked shift from 8:45pm until 12am (midnight) 3 hrs 45 min total, I would like to have this shift represented in the following manner using a formula:

Portion of shift between 8pm - 9pm 9pm - 10pm 10pm - 11pm 11pm - 12am Total
.75 hrs 1 hr 1 hr 1 hr 3.75

Does this make more sense?

Thank you