Rota spreadsheet - counting query

[S Burkey]

Hope someone can help as I'm at a total loss.

I've got a rota that shows all personnel details, with shift patterns:
9am - 3pm
8am - 4pm
10pm - 4am
etc (shift patterns subject to change and are individual)

I have a column that shows them 'clocked on' and another for 'clocked
off'.

Now for legal purposes I need to know who is 'on' at regular
intervals:

7 am
7.10 am
7.20 am
7.30 am

so thinking logically I'd say if for the 7 am count if they are
'clocked in' but not 'clocked off' then count that person but I can't
seem to find the right formula to ensure all personnel are counted.

Has anyone got any advice - it needs to be in excel for a number of
reasons

 

[Ian Digby]

Hello,
The answer to your problem uses array formulae. The only
thing different with these to ordinary formulae is that
you have to hold down Ctrl-Shift and press Enter to put
in the formula. This puts curly brackets round the
formula. It won't work if you manually put in the
curlies - you must always use Ctrl-Shift & Enter.

Here is how to do it:

Step 1:
In Cols A-C:
Name Shiftstart Shiftend
Joe Bloggs 09:00 15:00
Fred Smith 08:00 16:00
Ian Digby 09:00 15:00
Peter Brown 22:00 04:00
Tony Blair 08:00 16:00
Gordon Brown 08:00 16:00
etc....
** NB ** REMEMBER TO PUT THE TIMES IN AS SHOWN SO EXCEL
UNDERSTANDS THEY ARE TIMES, NOT NUMBERS.

Step 2:
Highlight all the data in cols A-C, then go "Insert Name
Create; Create names in Top Row" (make sure the other
options, Left Column etc, are un-checked).

Step 3:
In Cols E-F:
Time HeadCount
07:00 =COUNT(IF((Shiftstart<E2)*(Shiftend>E2),Shiftend))
07:10 =COUNT(IF((Shiftstart<E3)*(Shiftend>E3),Shiftend))
07:20 =COUNT(IF((Shiftstart<E4)*(Shiftend>E4),Shiftend))
etc......
** NB ** AS YOU ENTER EACH FORMULA IN COL F, HOLD DOWN
CTRL-SHIFT AND PRESS ENTER. CHECK THE CURLY BRACKETS HAVE
GONE IN.

Best of luck!

ID