Hi,
I am needing a formula to solve this problem..
I would like to count the number of staff that I have working for each hour of the day based on the individual staff position.
In my current spreadsheet I have a roster of staff that are split into different job types in column E and their start time in column K and finish time in column L.
Also the times of the day start at 7am and finish the following morning at 8am (yes this is 25 hours!!)
I have managed to do a total count using the following formula
=SUMPRODUCT(--(($K$7:$K$49<(BB17+BC17)/2)+($L$7:$L$49>(BB17+BC17)/2)+($K$7:$K$49>$L$7:$L$49)=2))
the cells BB17 and BC17 are the defining times of the day i.e. 07:00 for BB17 and 07:59 for BC17
and this works very well.
The staff positions are listed as numbers from 1-6
Any help would be much appreciated.
Bookmarks