VBA Question- Demand Analysis...How can I count activity by time of day?

[WonkaCEO]

Excel 2010

	A	B	C
1	Customer Number	In Date-Time	Out Date-Time
2	1	9/3/2015 15:00	9/4/2015 15:36
3	2	9/3/2015 15:20	9/4/2015 9:45
4	3	9/5/2015 12:15	9/7/2015 15:59
5	4	9/5/2015 15:00	9/6/2015 15:03
6	5	9/7/2015 8:00	9/8/2015 18:09
7	6	9/7/2015 13:00	9/9/2015 13:36
8	7	9/10/2015 11:00	9/13/2015 16:52
9	8	9/10/2015 10:23	9/11/2015 7:44
10	9	9/11/2015 7:00	9/12/2015 10:36
11	10	9/11/2015 8:00	9/11/2015 13:52

<tbody>
</tbody>

Data​

My data lists a series of customers leaving their cars for service. I'd like to develop VBA code that will count the number of cars that are present during each hour of the day.

I'm looking to create output that lists each hours of the day and the number of cars that were present during each hour. Any help would be greatly appreciated.

Here's a sample of the output:

Excel 2010

	A	B
1	Hour of Day	Count of Customers
2	00
3	01
4	02
5	03
6	04
7	05
8	06
9	07
10	08
11	09
12	10
13	11
14	12
15	13
16	14
17	15
18	16
19	17
20	18
21	19
22	20
23	21
24	22
25	23

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Output​

 

[petertenthije]

Are you sure the list should be 0 - 24? Should you not also have to add the date?

	A	B
1	Hour of day:	Count of customers:
2	9/3/2015 0:00	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A2;'DATA'!C:C;">="&'OUTPUT'!A2)
3	=$A2+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A3;'DATA'!C:C;">="&'OUTPUT'!A3)
4	=$A3+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A4;'DATA'!C:C;">="&'OUTPUT'!A4)
5	=$A4+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A5;'DATA'!C:C;">="&'OUTPUT'!A5)
6	=$A5+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A6;'DATA'!C:C;">="&'OUTPUT'!A6)
7	etc

<tbody>
</tbody>

Because list including date would grow very large you might want to consider creating a field in which you type the date, and that the table is updated based on that field. For instance in below table the date would be entered in B1:

	A	B
1	Input field for date	9/3/2015
2
3	Hour of day:	Count of customers:
4	=$B$1+0/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A4;'DATA'!C:C;">="&'OUTPUT'!A4)
5	=$A4+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A5;'DATA'!C:C;">="&'OUTPUT'!A5)
6	=$A5+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A6;'DATA'!C:C;">="&'OUTPUT'!A6)
7	=$A6+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A7;'DATA'!C:C;">="&'OUTPUT'!A7)
8	=$A7+1/24	=COUNTIFS('DATA'!B:B;"<="&'OUTPUT'!A8;'DATA'!C:C;">="&'OUTPUT'!A8)
9	etc

<tbody>
</tbody>

Edited to add row numbers and column headers to the tables

 

[WonkaCEO]

Peter,

I am ultimately looking to calculate the average number of cars in each hour per day. My intent of the output was to add the number of occurrences in each hour and divide by the total number of days in the study. Currently, I am looking to do this for a period of 1 year.

Thanks again for the help.

 

[petertenthije]

Ok, in that case I would need three helper columns on the source data. I would not be susprised if someone with more excel skills knows how to do it without helper columns (array formulas?), but here goes:

• Add a helper column to determine the entry time by using formula =HOUR(B#)
• Add a helper column to determine the departure time by using formula =HOUR(C#)
• Add a helper column to determine if a customer overnighted more then once. So only full 24h days where the car was parked are counted! If a car left on the same day as he arrived the value is 0. If the car left the first day after arrival the value is 0. If the car left the second day after arrival the value is 1. If the car left the third day after arrival the value is 2. If the car left the fourth day after arrival the value is 3, and so on. This will show how many customers have to be added to each time window (hour).

	A	B	C	D	E	F
1	Customer	In Date-Time	Out Date-Time	Entry time	Departure time	Amount of full (24h) days parked
2	1	9/3/2015 15:00	9/4/2015 15:36	=HOUR(B2)	=HOUR(C2)	=IF(ROUNDDOWN(C2-B2;0)=0;0;ROUNDDOWN(C2-B2;0)-1)
3	2	9/3/2015 15:20	9/4/2015 9:45	=HOUR(B3)	=HOUR(C3)	=IF(ROUNDDOWN(C3-B3;0)=0;0;ROUNDDOWN(C3-B3;0)-1)
4	3	9/5/2015 12:15	9/7/2015 15:59	=HOUR(B4)	=HOUR(C4)	=IF(ROUNDDOWN(C4-B4;0)=0;0;ROUNDDOWN(C4-B4;0)-1)
5	4	9/5/2015 15:00	9/6/2015 15:03	=HOUR(B5)	=HOUR(C5)	=IF(ROUNDDOWN(C5-B5;0)=0;0;ROUNDDOWN(C5-B5;0)-1)
6	5	9/7/2015 8:00	9/8/2015 18:09	=HOUR(B6)	=HOUR(C6)	=IF(ROUNDDOWN(C6-B6;0)=0;0;ROUNDDOWN(C6-B6;0)-1)
7	6	9/7/2015 13:00	9/9/2015 13:36	etc	etc	etc
8	7	9/10/2015 11:00	9/13/2015 16:52
9	8	9/10/2015 10:23	9/11/2015 7:44
10	9	9/11/2015 7:00	9/12/2015 10:36
11	10	9/11/2015 8:00	9/11/2015 13:52

<tbody>
</tbody>

The calculation of the output file works the same as in my first reply, except for two differences:

• The hour of day is now just number 0-24, instead of based on date/time. So no more 0/24, 1/24, 2/24 etc.
• To include the overnighting cars I have added a sum formula for all values in column F.

	A	B	C
1	Hour of Day	Count of Customers	Average per day
2	0	=COUNTIFS(DATA!D:D;"<="&OUTPUT!A2;DATA!E:E;">="&OUTPUT!A2)+SUM(DATA!F:F)	=B2/365
3	1	=COUNTIFS(DATA!D:D;"<="&OUTPUT!A3;DATA!E:E;">="&OUTPUT!A3)+SUM(DATA!F:F)	=B3/365
4	2	=COUNTIFS(DATA!D:D;"<="&OUTPUT!A4;DATA!E:E;">="&OUTPUT!A4)+SUM(DATA!F:F)	=B4/365
5	3	=COUNTIFS(DATA!D:D;"<="&OUTPUT!A5;DATA!E:E;">="&OUTPUT!A5)+SUM(DATA!F:F)	=B5/365
6	4	=COUNTIFS(DATA!D:D;"<="&OUTPUT!A6;DATA!E:E;">="&OUTPUT!A6)+SUM(DATA!F:F)	=B6/365
7	5	etc	etc
8	6
9	7
10	8
11	9
12	10
13	11
14	12
15	13
16	14
17	15
18	16
19	17
20	18
21	19
22	20
23	21
24	22
25	23

<tbody>
</tbody>

 

[WonkaCEO]

Thanks again for the help. You are definitely helping me get closer to the solution. Unfortunately, I may not be communicating the logic clearly. I hope this helps explain it.

Here's a sample of five:

Excel 2010

	A	B	C	D	E	F
1	Customer Number	In Date-Time	Out Date-Time	Entry time	Departure time	Amount of full (24h) days parked
2	1	9/3/2015 15:00	9/4/2015 15:36	15	15	0
3	2	9/3/2015 15:20	9/4/2015 9:45	15	9	0
4	3	9/5/2015 12:15	9/7/2015 15:59	12	15	1
5	4	9/5/2015 15:00	9/6/2015 15:03	15	15	0
6	5	9/7/2015 8:00	9/8/2015 18:09	8	18	0

<tbody>
</tbody>

Data​

Here's how I manually calculated the average number of cars per day in each hour:

Excel 2010

	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Manual Count By Hour	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
2	Customer 1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1
3	Customer 2	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	1	1	1	1	1	1	1	1	1
4	Customer 3	2	2	2	2	2	2	2	2	2	2	2	2	3	3	3	3	2	2	2	2	2	2	2	2
5	Customer 4	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1
6	Customer 5	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2	2	2	1	1	1	1	1
7	Total Occurrences	6	6	6	6	6	6	6	6	7	7	6	6	7	7	7	10	7	7	7	6	6	6	6	6
8	Total Days	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6
9	Avg Per Day	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.17	1.17	1.00	1.00	1.17	1.17	1.17	1.67	1.17	1.17	1.17	1.00	1.00	1.00	1.00	1.00

<tbody>
</tbody>

Data​

For example, Customer 1 entered in the 3 o'clock hour on Sept 3rd and left in the 3 o'clock hour on Sept 4th. When counting, the car was present each once during each hour and twice in the three o'clock hour during it's entire stay. My intent was to do this for each customer, then total the amount of occurrences in each hour and divide by the total number of days being reviewed.

I hope this helps clarify the logic. Thanks...

 

[petertenthije]

I see what you mean. The calculation works perfectly when the timeslot you enter is earlier then the timeslot you leave. But when the departure timeslot lies before the arrival timeslot it does not work. In other words, an arrival today at 16:00 and a departure the next day at 17:00 works fine,but an arrival today at 16:00 and a departure the next day at 15:00 will mess up the results.

I have modified the formula. It will now calculate the amount of hours on the day of arrival, the amount of hours on the day of departure (if different from arrival date), and the amount of full days a car has been parked.

The formulas are getting a bit long now, so I won't be posting them in the table but list them below:

• Column D
  =HOUR(B2),
  Simple formula to calculate the timeslot a car enters.
• Column E,
  =IF(ROUNDDOWN(C2;0)=ROUNDDOWN(B2;0);HOUR(C2);23)
  This calculates when the car leaves on the first day. If a car does NOT leave the same day it will always show 23. The departure date is then calculated in column G.
• Column F,
  =ROUNDDOWN(C2;0)-ROUNDDOWN(B2;0)-IF(ROUNDDOWN(C2;0)=ROUNDDOWN(B2;0);0;1)
  This column calculates the total amount of FULL days a car is in the garage.
• Column G,
  =IF(ROUNDDOWN(B2;0)=ROUNDDOWN(C2;0);"";HOUR(C2))
  If a car does not leave on the same day it arrives it will show the timeslot it left. If a car leaves the same day it arrives it will show "".

	A	B	C	D	E	F	G
1	Customer Number	In Date-Time	Out Date-Time	Start time	End time (arrival day)	Full days	End time (dep. date)
2	1	9/3/2015 15:00	9/4/2015 15:36
3	2	9/3/2015 15:20	9/4/2015 9:45
4	3	9/5/2015 12:15	9/7/2015 15:59
5	4	9/5/2015 15:00	9/6/2015 15:03

<tbody>
</tbody>

On the output file you need this formula:
=COUNTIFS(DATA!D:D;"<="&OUTPUT!A2;DATA!E:E;">="&OUTPUT!A2)+SUM(DATA!F:F)+COUNTIFS(DATA!G:G;">="&OUTPUT!A2)

 

[WonkaCEO]

It works!!! Thanks for helping me out with this. Your solution is much easier than the VBA I was attempting to write. Have a great holiday weekend!