Rota spreadsheet - counting query

[polgara]

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
reason

 

[Bill Irving]

Have you tried this?

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