Counting employees logged in during specific hours for a specific amount of time

[amulder]

Hi,


I am banging my head against the wall on this issue. Below is my sample dataset:


Employee ID LoginHr LogoutHr
123 01/01/2017 09:29:31 01/01/2017 17:51:10
234 01/01/2017 03:11:01 01/01/2017 14:10:31
456 01/01/2017 23:59:59 01/02/2017 06:00:32
789 01/02/2017 07:00:09 01/02/2017 07:05:11


What I need to do is count the number of employees who were logged in for each hour of each day and within each of those hours only count the employees who were logged in for 15 minutes or more. So my result should look something like this:


Date Count of Logged in Employees
01/01/2017 09:00:00 2
01/01/2017 10:00:00 2
...
01/01/2017 13:00:00 2
01/01/2017 14:00:00 1
...
01/01/2017 17:00:00 1
01/01/2017 18:00:00 0
...
01/01/2017 23:00:00 1
...
01/02/2017 05:00:00 1
01/02/2017 06:00:00 0
01/02/2017 07:00:00 0


I have setup a Date table that has the dates/times and then am using a calculation to reference back to the original data set (Employees) to count the number of rows, but am not getting correct results. Can anyone help me find a solution:


=CALCULATE(COUNTROWS('Employees'),FILTER('Employees',('Employees'[LOGINHR] >= 'Date'[DATE_HOUR])&&'Employees'[LOGOUTHR]<= 'Date'[DATE_HOUR]))

 

[LloydFinancials]

See if this helps.

Excel 2010
	A	B	C	D	E	F	G
1	Employee ID	LoginHr	LogoutHr
2	123	1/1/17 9:29	1/1/17 17:51			1/1/17 10:00	2
3	234	1/1/17 3:11	1/1/17 14:10			1/2/17 5:00	1
4	456	1/1/17 23:59	1/2/17 8:00			1/2/17 7:00	2
5	789	1/2/17 7:00	1/2/17 7:05			1/4/17 10:00	-
Sheet1

Cell Formulas
Range	Formula
G2	=COUNTIFS($B$2:$B$5,"<="&F2,$C$2:$C$5,">="&F2)