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

 

[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.