SOLVED

Important formula in Excel

New Contributor

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 plateEntry dateExit dateSlot limit:2
TX17922022-06-30 14:07:572022-06-30 17:00:49  
TX10302022-06-30 14:01:352022-06-30 18:32:16  
TX12882022-06-30 11:22:232022-06-30 17:04:14  
TX15352022-06-30 10:17:412022-06-30 16:54:14  
TX10082022-06-30 9:48:402022-06-30 17:35:46  
TX12882022-06-30 9:31:532022-06-30 10:59:33  
TX13992022-06-30 9:27:412022-06-30 17:09:45  
TX17712022-06-30 9:23:572022-06-30 19:31:00  
TX18912022-06-30 9:06:482022-06-30 16:38:44  
TX17922022-06-29 13:04:052022-06-30 13:49:59  
TX10302022-06-29 12:27:432022-06-29 18:50:12  
TX17922022-06-29 11:38:042022-06-29 11:49:18  
TX15352022-06-29 10:19:052022-06-29 17:29:14  
TX13992022-06-29 9:58:082022-06-29 18:03:11  
TX10082022-06-29 9:29:272022-06-29 18:06:48  
TX17712022-06-29 9:28:162022-06-29 19:59:47  
TX17922022-06-29 9:02:322022-06-29 9:05:21  
TX17922022-06-28 14:34:322022-06-28 15:54:19  
TX17922022-06-28 12:15:582022-06-28 13:27:19  
TX10302022-06-28 10:46:202022-06-28 18:32:34  
TX15352022-06-28 10:20:112022-06-28 17:40:53  
TX17922022-06-28 10:02:132022-06-28 12:00:59  
TX10082022-06-28 9:55:452022-06-28 18:42:07  
TX13992022-06-28 9:48:222022-06-28 17:22:45  
TX17712022-06-28 9:08:072022-06-28 18:54:53  
TX17922022-06-27 15:05:502022-06-27 18:19:41  
4 Replies
Up.
best response confirmed by Hans Vogelaar (MVP)
Solution

@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:

Riny_van_Eekelen_0-1658138328985.png

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

 

 

 

Hello,

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

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

https://exceloffthegrid.com/power-query-introduction/