Formula to calculate how many cars are parked at a point in time

Hi Everyone,

So first post here, I'm looking for a formula that I feel like should be easy to work out but so far cannot really find a solution. Basically my place of work wants to know how many cars are parked on site at particular points in time, I've got arrival/departure times for the cars as well as hours parked on site but am really stumped with this one.

In the example attached the first table show the data I have, the table next to it show the information that I want to calculate automatically.

Any help would be greatly appreciated

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Converted your text times to Excel time format

E2

=TIME(LEFT($B2,LEN($B2)-2),RIGHT($B2,2),0)

F2

=IF(TIME(LEFT($C2,LEN($C2)-2),RIGHT($C2,2),0)=0,TIME(23,59,0),TIME(LEFT($C2,LEN($C2)-2),RIGHT($C2,2),0))

Cars per hour

=SUMPRODUCT((K2>=$E$2:$E$16)*(K2<=$F$2:$F$16))

You might consider entering 2400 as 2359 as it makes it easier to compute as Excel treats 2400 as 0

so F2

=TIME(LEFT($C2,LEN($C2)-2),RIGHT($C2,2),0)

Better still, enter as Excel times initially!

[.... withdrawn ....]

@curiouscat408: works fine!