Excel Formula

[Peter Plant]

The problem I am trying to solve is... Over a single day i have two data columns, one is a staff start time and the other is staff end time. 

What I want to do is ascertain how many staff are present at a specific 15 minute time segment of the day, starting at 7:00:00 up to 22:00:00. 

So looking at the times an individual was present, were they there at 7.15, 7.30, 7.45 etc. I am looking to use this for trended attendance stats

I was trying the COUNTIF function, but i'm failing to get the right formula, any advice anyone

Thank you

[#2]

Hi Peter,

For data structured like this

the formula could be

=SUMPRODUCT(($C$3:$C$6<=F3)*($D$3:$D$6>=(F3+1/24/4)))

[#3]

Hi Sergei
Thanks for your reply, I tried your formula but It seems like the people leaving are not being deducted off the count.

X Axis Y Axis Should be
jones 08-Feb 08:15:00 10:10:15 1:55:15 07:00:00 0
smith 08-Feb 08:15:23 10:57:36 2:42:13 07:15:00 0
connor 08-Feb 08:15:23 08:21:36 0:06:13 07:30:00 0
dobson 08-Feb 08:15:23 17:32:12 9:16:49 07:45:00 0
jenkins 08-Feb 08:15:23 13:19:12 5:03:49 08:00:00 0
wilson 08-Feb 08:15:23 18:13:45 9:58:22 08:15:00 1 1
connor 08-Feb 08:35:23 12:16:48 3:41:25 08:30:00 6 5
jones 08-Feb 10:23:15 11:02:45 0:39:30 08:45:00 7 5
jones 08-Feb 11:15:23 11:23:48 0:08:25 09:00:00 7 5
smith 08-Feb 11:15:23 13:26:24 2:11:01 09:15:00 7 5
jones 08-Feb 12:41:12 18:26:24 5:45:12 09:30:00 7 5
connor 08-Feb 13:15:23 18:02:24 4:47:01 09:45:00 7 5
jenkins 08-Feb 14:05:14 18:16:24 4:11:10 10:00:00 6 5
connor 08-Feb 18:15:23 21:09:36 2:54:13 10:15:00 6 0

I am getting this at the moment, it seems the people leaving are not being deducted off the count

[#4]

Apologies I copied and pasted from Excel, the final figure is what the number should be, the one before it is what the formula is giving

[#5]

No problem, I copied back into the Excel. So, for time interval in yellow we shall calculate people in green, correct?

[#6]

Yes that's correct Sergei

[#7]

Peter, perhaps I misunderstood something, but formula gives correct result. I applied it to your table

=SUMPRODUCT(($D$2:$D$15<=$G2)*($E$2:$E$15>=($G2+1/24/4)))

it gives

For me not clear why, for example for interval  from 09:00 to 09:15, it Shall be 5 person in, I counted 6 (in orange). Please clarify.

[#8]

Hi Sergei

Yes it should be 6, 5 is incorrect. I copied and pasted your formula, changed the cell addresses to match where my data is sitting, but I am still getting 7 in at 9am when it should be 6. I am doing something wrong

[#9]

Peter, please be sure you correctly changed addresses keeping logic of absolute and relative references. Another point is < instead of <=, etc. Have no idea what else could be.

[#10]

Thanks Sergei, in your illustration column J looks correct, is that based on the formula you are showing