Calculate Number of Employees Who Were At Work Each Hour?

[cr2crf]

I have been all over trying to figure this out. I attached a link at the bottom of this post to one discussion that was helpful but didn't solve my problem.

I need to know how many employees I have working each hour of the day based on a 24/7 business with swing shifts and graveyard shifts.

Below is the layout of the sheet going from left to right, A to I and top to bottom, from 1 to 12.

One of the problems I may be having is dealing with military time and the shifts that go past midnight (working from 8pm to 10am). I hard entered the numbers just to see if something would work. Any ideas on how to better format the numbers would be helpful.

This is the formula that I have been working with. It's located in cell D3:

=IF((AND($B3>=D$1, $C3<=D$2)),0,1)

0 100 200 300 400 500
Start Time End Time 100 200 300 400 500 600
emp1 830 430 1 1 1 1 0 0
emp2 1730 530 1 1 1 1 1 0
emp3 1330 130 1 0 0 0 0 0
emp4 1400 230 1 1 0 0 0 0
emp5 1530 330 1 1 1 0 0 0
emp6 1200 2400 1 1 1 1 1 1
emp7 2401 1200 1 1 1 1 1 1
emp8 900 1900 1 1 1 1 1 1
emp9 2000 1000 1 1 1 1 1 1
emp10 1730 500 1 1 1 1 0 0



http://www.excelforum.com/excel-form...each-hour.html

 

[Scott R]

How do you want to handle the count when an employee starts or stops at a point other than the top of the hour?

 

[Scott R]

This gives you a start. You may have to round the Start and End times to accommodate half-hours.
Put this in D3:

=IF($B3<$C3, IF(AND($B3 < D$2, $C3 > D$1), 1, 0), IF(OR($B3 < D$2, $C3 > D$1), 1, 0))

 

[cr2crf]

Thanks, Scott.