Running Count by Hour of the Day

[dblackwood]

Hello,

I have a problem that I cannot solve... I have a dataset that includes an "Arrival Time", an "Exit Time", and a calculated "Hours in Department". I am trying to calculate to number of visits that are currently in the department at each hour of the day.

Example: I have 3 visits that come in at 1:00 and they stay for 3 hours, I have 2 visits that come in at 2:00 and they stay for 1 hour. The count for 1:00 = 3, 2:00 = 5 (because the 1:00 visits are still present), 3:00 = 5, 4:00 = 3, 5:00 = 0.

I have assumed it would involve a countifs or sumproduct function, but I cannot get the duration time to factor.

Thanks for listening and hopefully a solution!!

Sorry, I do not have permissions to attach a example file at this time.

David

 

[FormR]

Hi, welcome to the forum!

Something like this maybe..

Excel 2013/2016
	A	B	C	D	E	F
1	Visit	In	Out		Hour	# Visits
2	1	01:00	04:00		01:00	3
3	2	01:00	04:00		02:00	5
4	3	01:00	04:00		03:00	5
5	4	02:00	03:00		04:00	3
6	5	02:00	03:00		05:00	0
Sheet1

Cell Formulas
Range	Formula
F2	=COUNTIFS(B:B,"<="&E2,C:C,">="&E2)