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

 

[steve the fish]

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

Hi. You cant have number of cars in the car park at a time interval eg 00:00 to 00:59. You can however have number of cars in the car park at a moment in time eg 00:00

 

[Jampa]

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

Thanks Steve for your prompt reply. Ideally i like to have the total number of car in that time slot, i thought may be there is way with If / between function. anyhow what you mentioned would be a good start for me. would you be able to provide the formula for that ?
Cheers

 

[steve the fish]

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

A time interval just wouldnt work. There would be cars in and out in that interval. Its a fluid thing so a moment in time is the only solution. Anyway pasting your first table into A1:B13 and the second into G1:M49 try this in J2 and copy down:

=COUNTIFS(A:A,"<="&G2+H2,B:B,">="&G2+H2)

The occupancy rate is simply the result of the above divided by the total number of spaces.

 

[AlanY]

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

A time interval just wouldnt work. There would be cars in and out in that interval. Its a fluid thing so a moment in time is the only solution. Anyway pasting your first table into A1:B13 and the second into G1:M49 try this in J2 and copy down:

=COUNTIFS(A:A,"<="&G2+H2,B:B,">="&G2+H2)

The occupancy rate is simply the result of the above divided by the total number of spaces.

one problem is that it didn't account for cars that enter after the start and before the end period

 

[steve the fish]

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

one problem is that it didn't account for cars that enter after the start and before the end period

Thats because, as i say, there is no end... its a moment in time.

 

[AlanY]

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

Thats because, as i say, there is no end... its a moment in time.

but sure, that should be enough data there to work out how many cars in the car park.

said from the guy still wouldn't find a solution,

 

[steve the fish]

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

How could you work out how many cars were there for an interval? Its fluid.

 

[AlanY]

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

How could you work out how many cars were there for an interval? Its fluid.

I'm working on it, let say between time T1 and T2, the cars will be there if

1) car enter before T1 and leave after T1,
2) car enter between T1 and T2
3) car leave between T1 and T2

the problem is I'm not sure how to combine them together

 

[steve the fish]

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.