Car Park Occupancy rate

[Jampa]

Hello Everyone,

Could some genius please help me with formula or ways to find a daily occupancy rate of car park in hourly bases based on transaction of “Date Time IN” and “Date Time OUT”. ?

Below are the actual sample of transaction we receive.

Note: Drivers can park for more than a day as well.

Session Start	Session End	Duration (in Mins)	VRN	Car Park
01/04/2018 04:22	01/04/2018 17:59	817	****UXZ	Car Park 1
01/04/2018 05:18	01/04/2018 17:59	761	****OXZ	Car Park 1
01/04/2018 05:53	01/04/2018 17:59	726	****PTZ	Car Park 1
01/04/2018 06:03	01/04/2018 17:59	716	****MGZ	Car Park 1
01/04/2018 06:15	01/04/2018 17:59	704	****LBZ	Car Park 1
01/04/2018 06:53	01/04/2018 11:00	247	****SKZ	Car Park 1
01/04/2018 06:57	01/04/2018 15:00	483	****FBZ	Car Park 1
01/04/2018 07:03	01/04/2018 17:59	656	****KDZ	Car Park 1
01/04/2018 07:10	01/04/2018 17:59	649	****EHZ	Car Park 1
01/04/2018 20:40	02/05/2018 18:59	1339	****ZHZ	Car Park 1
01/04/2018 20:44	02/05/2018 18:59	1335	****JMZ	Car Park 1
01/04/2018 21:14	02/05/2018 16:59	1185	****FFZ	Car Park 1

<tbody>
</tbody>

Below is the table that would be in a different sheet and I need the data in column D (No of car in the car park) using formula/function based on above transactions.

Date	Time Period	No of car in car park	Total space	space available	Occupancy rate in %
01/04/2018	00:00	00:59	0	100	100	0%
01/04/2018	01:00	01:59	1	100	99	1%
01/04/2018	02:00	02:59	2	100	98	2%
01/04/2018	03:00	03:59	3	100	97	3%
01/04/2018	04:00	04:59	4	100	96	4%
01/04/2018	05:00	05:59	5	100	95	5%
01/04/2018	06:00	06:59	6	100	94	6%
01/04/2018	07:00	07:59	7	100	93	7%
01/04/2018	08:00	08:59	8	100	92	8%
01/04/2018	09:00	09:59	9	100	91	9%
01/04/2018	10:00	10:59	10	100	90	10%
01/04/2018	11:00	11:59	11	100	89	11%
01/04/2018	12:00	12:59	12	100	88	12%
01/04/2018	13:00	13:59	13	100	87	13%
01/04/2018	14:00	14:59	14	100	86	14%
01/04/2018	15:00	15:59	15	100	85	15%
01/04/2018	16:00	16:59	16	100	84	16%
01/04/2018	17:00	17:59	17	100	83	17%
01/04/2018	18:00	18:59	18	100	82	18%
01/04/2018	19:00	19:59	19	100	81	19%
01/04/2018	20:00	20:59	20	100	80	20%
01/04/2018	21:00	21:59	21	100	79	21%
01/04/2018	22:00	22:59	22	100	78	22%
01/04/2018	23:00	23:59	23	100	77	23%
02/04/2018	00:00	00:59	24	100	76	24%
02/04/2018	01:00	01:59	25	100	75	25%
02/04/2018	02:00	02:59	26	100	74	26%
02/04/2018	03:00	03:59	27	100	73	27%
02/04/2018	04:00	04:59	28	100	72	28%
02/04/2018	05:00	05:59	29	100	71	29%
02/04/2018	06:00	06:59	30	100	70	30%
02/04/2018	07:00	07:59	31	100	69	31%
02/04/2018	08:00	08:59	32	100	68	32%
02/04/2018	09:00	09:59	33	100	67	33%
02/04/2018	10:00	10:59	34	100	66	34%
02/04/2018	11:00	11:59	35	100	65	35%
02/04/2018	12:00	12:59	36	100	64	36%
02/04/2018	13:00	13:59	37	100	63	37%
02/04/2018	14:00	14:59	38	100	62	38%
02/04/2018	15:00	15:59	39	100	61	39%
02/04/2018	16:00	16:59	40	100	60	40%
02/04/2018	17:00	17:59	41	100	59	41%
02/04/2018	18:00	18:59	42	100	58	42%
02/04/2018	19:00	19:59	43	100	57	43%
02/04/2018	20:00	20:59	44	100	56	44%
02/04/2018	21:00	21:59	45	100	55	45%
02/04/2018	22:00	22:59	46	100	54	46%
02/04/2018	23:00	23:59	47	100	53	47%

<tbody>
</tbody>

I hope I have explained it clearly above, if there is anything that doesn’t make sense or not clear enough, please can you let me know, so I can try give more details.

Many thanks in advance for your time and help.

Much appreciated

 

[AlanY]

Re: How to calculate Car Park Occupancy rate - Help !!!

I think you will be there a while to say x number of cars were there between two times. Its not possible unless they are only allowed in on the hour and have to leave at 59 minutes past the hour. You could work out an average i suppose but the average would be an average of moments in time within the interval.

you might be right, but i'm not giving up just yet

 

[AlanY]

Re: How to calculate Car Park Occupancy rate - Help !!!

try this

Excel 2013/2016
	A	B	F	G	H	I	J
1	Session Start	Session End		Date	Time Period		No of car in car park
2	01/04/2018 04:22	01/04/2018 17:59		01/04/2018	00:00	00:59	0
3	01/04/2018 05:18	01/04/2018 17:59		01/04/2018	01:00	01:59	0
4	01/04/2018 05:53	01/04/2018 17:59		01/04/2018	02:00	02:59	0
5	01/04/2018 06:03	01/04/2018 17:59		01/04/2018	03:00	03:59	0
6	01/04/2018 06:15	01/04/2018 17:59		01/04/2018	04:00	04:59	1
7	01/04/2018 06:53	01/04/2018 11:00		01/04/2018	05:00	05:59	3
8	01/04/2018 06:57	01/04/2018 15:00		01/04/2018	06:00	06:59	7
9	01/04/2018 07:03	01/04/2018 17:59		01/04/2018	07:00	07:59	9
10	01/04/2018 07:10	01/04/2018 17:59		01/04/2018	08:00	08:59	10
11	01/04/2018 08:10	02/04/2018 18:59		01/04/2018	09:00	09:59	10
12	01/04/2018 20:44	02/04/2018 18:59		01/04/2018	10:00	10:59	10
13	01/04/2018 21:14	02/04/2018 16:59		01/04/2018	11:00	11:59	10
14				01/04/2018	12:00	12:59	9
15				01/04/2018	13:00	13:59	9
16				01/04/2018	14:00	14:59	9
17				01/04/2018	15:00	15:59	9
18				01/04/2018	16:00	16:59	8
19				01/04/2018	17:00	17:59	8
20				01/04/2018	18:00	18:59	1
21				01/04/2018	19:00	19:59	1
22				01/04/2018	20:00	20:59	2
23				01/04/2018	21:00	21:59	3
24				01/04/2018	22:00	22:59	3
25				01/04/2018	23:00	23:59	3
26				02/04/2018	00:00	00:59	3
27				02/04/2018	01:00	01:59	3
28				02/04/2018	02:00	02:59	3
29				02/04/2018	03:00	03:59	3
30				02/04/2018	04:00	04:59	3
31				02/04/2018	05:00	05:59	3
32				02/04/2018	06:00	06:59	3
33				02/04/2018	07:00	07:59	3
34				02/04/2018	08:00	08:59	3
35				02/04/2018	09:00	09:59	3
36				02/04/2018	10:00	10:59	3
37				02/04/2018	11:00	11:59	3
38				02/04/2018	12:00	12:59	3
39				02/04/2018	13:00	13:59	3
40				02/04/2018	14:00	14:59	3
41				02/04/2018	15:00	15:59	3
42				02/04/2018	16:00	16:59	3
43				02/04/2018	17:00	17:59	2
44				02/04/2018	18:00	18:59	2
45				02/04/2018	19:00	19:59	0
46				02/04/2018	20:00	20:59	0
47				02/04/2018	21:00	21:59	0
48				02/04/2018	22:00	22:59	0
49				02/04/2018	23:00	23:59	0
Sheet3

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(--($A$2:$A$13<=(G2+H2)),(--($B$2:$B$13>=(G2+H2))))+SUMPRODUCT(--($A$2:$A$13>=(G2+H2)),(--($A$2:$A$13<=(G2+I2))))

 

[steve the fish]

Re: How to calculate Car Park Occupancy rate - Help !!!

Ill put this to you. If i was to ask the question 'how many cars are there in the car park next week' you would think i had gone mad. It makes no sense. Next week is a time interval just the same as these hourly ones. They make no sense either.

 

[AlanY]

Re: How to calculate Car Park Occupancy rate - Help !!!

but it's not a prediction for future, is it? At least I hope it isn't.

just a data analysis based on past record.

 

[steve the fish]

Re: How to calculate Car Park Occupancy rate - Help !!!

Ok 'how many cars were there in the car park last week'. Still makes no sense. Its a question that cant be answered is what im trying to say.

 

[AlanY]

Re: How to calculate Car Park Occupancy rate - Help !!!

Ok 'how many cars were there in the car park last week'. Still makes no sense. Its a question that cant be answered is what im trying to say.

well, what I call tell you or the OP is

there are 10 cars parked there on 01/04/2018 between 08:00 and 08:59 , or
8 between 17:00 and 17:59 etc

I believe this is what the OP asked for, i may be wrong though

 

[Jampa]

Re: How to calculate Car Park Occupancy rate - Help !!!

Hi Alan,

Thanks very much for your help with this. I think you have resolved my query. you are star.

Hi Steve,

Thanks for your help and input as well. I may have not been clear. i only need to look at the past data and tell how busy the car park was, and analyse the average occupancy rate based on hourly occupancy. so i think Alan solution would solve my problem.

Thank you both. I can now go back and load the whole load of data and start analysing.

 

[steve the fish]

Re: How to calculate Car Park Occupancy rate - Help !!!

Ok try this example. How many were parked there between:

01/04/2018 04:00 and 01/04/2018 04:59.

At 04:00 there were none. At 04:10 there were none. At 4:20 there were none. At 4:30 there was 1. etc etc. So is the answer 1 or is it 0? The answer is actually there is no answer. The question is nonsensical. The number of cars parked is only possible to answer at a moment in time not a time interval. You could say there was an average of just less than 0.5 between the two times.

 

[AlanY]

Re: How to calculate Car Park Occupancy rate - Help !!!

Ok try this example. How many were parked there between:

01/04/2018 04:00 and 01/04/2018 04:59.

At 04:00 there were none. At 04:10 there were none. At 4:20 there were none. At 4:30 there was 1. etc etc. So is the answer 1 or is it 0? The answer is actually there is no answer. The question is nonsensical. The number of cars parked is only possible to answer at a moment in time not a time interval. You could say there was an average of just less than 0.5 between the two times.

only now I realised what are you saying.

ok, the answer to a different question is

there is 1 car max. during that period 01/04/2018 04:00 and 01/04/2018 04:59.

again, we are not sure what exactly the OP after

 

[AlanY]

Re: How to calculate Car Park Occupancy rate - Help !!!

even that is not right!

if 1 left at 4:15 and another one enter on 4:30 that will b**8 off the numbers.
time to go home