Forum Discussion
Excel Formula
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
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)))
- 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- 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
