Breakdown of employees by hour

[RJ209]

Hi everyone,

I want to calculate an automated breakdown of the total number of people working at any given time throughout the day when I have only their SOS (Start Of Shift) and EOS (End Of Shift) times, as well as total hours worked. So for a very basic example if I have one person working 1200-1800, another 1100-1900 and someone else 0900-1500 how many total people are working at 1600? Is there a way to calculate this using Excel?

Thanks in advance I appreciate any help!

 

[jasonb75]

Assuming that nobody starts before midnight and finishes after, the simple version of the formula would be

=COUNTIFS(B:B,"<="&F2,C:C,">"&F2)

This will count people that start at the time in column F or are already working, but not those that finish at the time in column F.

 

[RJ209]

Hi Jason, thanks for your help. You're right nobody starts before midnight and finishes after. I've tried the above formula but I can't seem to get it to work?

 

[jasonb75]

It looks like your SOS and EOS times are formatted as text, which will cause problems with formulas.

If it is a simple format error then the easiest way to fix that is with text to columns.

Select column B (the whole column). Go to the Data tab on the excel ribbon.
Click Text to Columns, then Next. Uncheck all boxes then click Next, then Finish.
Repeat for column C.

 

[RJ209]

Hmmm issue is still occurring.

 

[jasonb75]

Try the same on column F.

I tried a simple test and it is working for me.

Note that it will not work if any of the cells contain dates as well as times. A date that is not visible due to formatting is still visible to formulas.

 

[RJ209]

Brilliant! Formatting was the issue all solved. Everything works now thank you very much you've been a massive help