SOLVED

New Contributor

# Important formula in Excel

Hello,

We have a license plate, entry, exit date and slot limit. I need to calculate who enter the parking when there was no space e.g two cars inside parking, third one has enter as "overslot" and I need to know which one and when.

 License plate Entry date Exit date Slot limit: 2 TX1792 2022-06-30 14:07:57 2022-06-30 17:00:49 TX1030 2022-06-30 14:01:35 2022-06-30 18:32:16 TX1288 2022-06-30 11:22:23 2022-06-30 17:04:14 TX1535 2022-06-30 10:17:41 2022-06-30 16:54:14 TX1008 2022-06-30 9:48:40 2022-06-30 17:35:46 TX1288 2022-06-30 9:31:53 2022-06-30 10:59:33 TX1399 2022-06-30 9:27:41 2022-06-30 17:09:45 TX1771 2022-06-30 9:23:57 2022-06-30 19:31:00 TX1891 2022-06-30 9:06:48 2022-06-30 16:38:44 TX1792 2022-06-29 13:04:05 2022-06-30 13:49:59 TX1030 2022-06-29 12:27:43 2022-06-29 18:50:12 TX1792 2022-06-29 11:38:04 2022-06-29 11:49:18 TX1535 2022-06-29 10:19:05 2022-06-29 17:29:14 TX1399 2022-06-29 9:58:08 2022-06-29 18:03:11 TX1008 2022-06-29 9:29:27 2022-06-29 18:06:48 TX1771 2022-06-29 9:28:16 2022-06-29 19:59:47 TX1792 2022-06-29 9:02:32 2022-06-29 9:05:21 TX1792 2022-06-28 14:34:32 2022-06-28 15:54:19 TX1792 2022-06-28 12:15:58 2022-06-28 13:27:19 TX1030 2022-06-28 10:46:20 2022-06-28 18:32:34 TX1535 2022-06-28 10:20:11 2022-06-28 17:40:53 TX1792 2022-06-28 10:02:13 2022-06-28 12:00:59 TX1008 2022-06-28 9:55:45 2022-06-28 18:42:07 TX1399 2022-06-28 9:48:22 2022-06-28 17:22:45 TX1771 2022-06-28 9:08:07 2022-06-28 18:54:53 TX1792 2022-06-27 15:05:50 2022-06-27 18:19:41
4 Replies

# Re: Important formula in Excel

Up.
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Important formula in Excel

@Papajus I'm not fully understanding your question, but perhaps the attached file helps. I pulled the table into Power Query and re-shaped it a bit. It should enable you to see entries and exits in chronological order. The first couple of rows look like this:

E.g. on the 28th, 6 cars entered between 9:08 and 10:46. When you say that you only have two slots, the last 4 were "overslot".

# Re: Important formula in Excel

Hello,

Thank you. It's exactly what I'm looking for and it's seems I need to study about "Power Query"..

# Re: Important formula in Excel

@Papajus Glad I could help. The site in the link below would be a good place to start.