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.

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

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

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