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 !!!

Id use what i had originally and then if i needed more detail id merely use more times. Instead of 0:00, 1:00 etc use 0:00, 0:01 etc. They could then be used as an average if interval was rearly required such as making a graphical representation.