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