Formula to count employees on schedule over 24 hour period

Hi All,

I create a schedule of around 25 people in a 24 hour department. What I am trying to do is find the formula to automatically generate the chart under the schedule showing how many employees are on the schedule every 2 hours throughout the day. Currently this data is entered by counting the employees manually and typing it in.

I have attached a sample of the original schedule I use first, which I cannot seem to get to work because I have the starting time and ending time in the same cell (i.e. 10am-6pm).

Then I have attached a manipulated schedule, which divides the start time and end time into 2 columns, switches to army time, then uses the formula:

{=SUM(IF(A54>=B7:B31,IF(A54<C7:C31,1,0),0))}

This nearly achieves what I am trying to do, however, it trips up when the shift hits 12AM and the formula no longer holds true.

It seems simple enough, but I am missing something. Any help would be greatly appreciated!

Thanks,
Brijsek

see i attached file and caculation is on simple base

10 am 1
12 pm 1
2 pm 1 sum of (10 am+12 pm+ 2 pm) 3

like wise

Morevijay,

Thanks for taking a look! The formula you've used does works for the data I presented, however, I should have given a broader view of the entire week. Your formula seems to work based on the employee working an 8 hour shift, but I have multiple employees who only work 6 hour shifts (TIMES IN BOLD).

I have attached a sample including a full week schedule, along with a chart at the bottom of the schedule using the {=SUM(IF(A39>=B7:B31,IF(A39<C7:C31,1,0),0))} formula to generate the Employee Count list. As you can see, when the shift overlaps the 12AM hour, the formula no longer holds true, and the values start returning 0 (highlighted in DARK PINK).

Any additional assistance would be greatly appreciated!

Brijsek

I hate to bump a thread, but I am still scratching my head trying to resolve this formula. Anyone??

This is perfect!! Thank you so much JeteMc and Morevijay for taking the time to help me out!

Hello all. This is my first post, so apologise if this is against the rules. This thread has been most helpful to me, but I do have one question. I have followed the example from JeteMc but I have an issue with my sheet. Anything past midnight should be classed as the next day. How can I have this running though the days of the week? Any assistance or directions for help on a solution would be amazing.