Formula Help with overlapping times

[yessir]

I am trying to find a formula that would help me find how many cars overlap and are in the lot at the same time but only when column D is False. I would then want to count how many cars are in the lot at the same time. The start times are located in B2:B7 and end times are in C2:C7. I've tried using a SUMPRODUCT function but was wondering if there was an easier way to go about this and include the function to count the number of cars that overlap.
A B C D
[TABLE="width: 567"]
<tbody>[TR]
[TD]Arvl Lot Location[/TD]
[TD] Arvl Time[/TD]
[TD] Dept Time[/TD]
[TD]Is Overnight[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]15:25[/TD]
[TD]16:10[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]21:10[/TD]
[TD]22:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]8:21[/TD]
[TD]9:19[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]11:08[/TD]
[TD]11:45[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]14:29[/TD]
[TD]15:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]19:55[/TD]
[TD]21:20[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

My last attempt...

A possible solution using helper columns

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Arvl Place​

[/TD]
[TD]

Arvl Time​

[/TD]
[TD]

Dept Time​

[/TD]
[TD]

Helper1​

[/TD]
[TD]

Helper2​

[/TD]
[TD]

Is Overnight​

[/TD]
[TD]

Num of Overlaps​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

jjj​

[/TD]
[TD]

20:09​

[/TD]
[TD]

06:33​

[/TD]
[TD]

20,15​

[/TD]
[TD]

30,55​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]jjj[/TD]
[TD]

11:05​

[/TD]
[TD]

12:03​

[/TD]
[TD]

11,08​

[/TD]
[TD]

12,05​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

jjj​

[/TD]
[TD]

09:26​

[/TD]
[TD]

10:16​

[/TD]
[TD]

9,43​

[/TD]
[TD]

10,27​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

jjj​

[/TD]
[TD]

23:52​

[/TD]
[TD]

01:15​

[/TD]
[TD]

23,87​

[/TD]
[TD]

25,25​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

jjj​

[/TD]
[TD]

15:13​

[/TD]
[TD]

16:03​

[/TD]
[TD]

15,22​

[/TD]
[TD]

16,05​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

jjj​

[/TD]
[TD]

22:02​

[/TD]
[TD]

05:00​

[/TD]
[TD]

22,03​

[/TD]
[TD]

29,00​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

jjj​

[/TD]
[TD]

18:26​

[/TD]
[TD]

06:45​

[/TD]
[TD]

18,43​

[/TD]
[TD]

30,75​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

jjj​

[/TD]
[TD]

20:06​

[/TD]
[TD]

06:00​

[/TD]
[TD]

20,10​

[/TD]
[TD]

30,00​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

jjj​

[/TD]
[TD]

15:51​

[/TD]
[TD]

16:41​

[/TD]
[TD]

15,85​

[/TD]
[TD]

16,68​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

jjj​

[/TD]
[TD]

11:52​

[/TD]
[TD]

12:48​

[/TD]
[TD]

11,87​

[/TD]
[TD]

12,80​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

jjj​

[/TD]
[TD]

08:03​

[/TD]
[TD]

08:53​

[/TD]
[TD]

8,05​

[/TD]
[TD]

8,88​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

jjj​

[/TD]
[TD]

13:30​

[/TD]
[TD]

14:20​

[/TD]
[TD]

13,50​

[/TD]
[TD]

14,33​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

jjj​

[/TD]
[TD]

17:52​

[/TD]
[TD]

18:42​

[/TD]
[TD]

17,87​

[/TD]
[TD]

18,70​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

jjj​

[/TD]
[TD]

10:02​

[/TD]
[TD]

10:47​

[/TD]
[TD]

10,03​

[/TD]
[TD]

10,78​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

jjj​

[/TD]
[TD]

18:28​

[/TD]
[TD]

19:40​

[/TD]
[TD]

18,47​

[/TD]
[TD]

19,67​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]jjj[/TD]
[TD]

22:55​

[/TD]
[TD]

06:00​

[/TD]
[TD]

22,92​

[/TD]
[TD]

30,00​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

jjj​

[/TD]
[TD]

13:39​

[/TD]
[TD]

14:21​

[/TD]
[TD]

13,65​

[/TD]
[TD]

14,35​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

jjj​

[/TD]
[TD]

19:03​

[/TD]
[TD]

19:45​

[/TD]
[TD]

19,05​

[/TD]
[TD]

19,75​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

jjj​

[/TD]
[TD]

09:31​

[/TD]
[TD]

10:21​

[/TD]
[TD]

9,52​

[/TD]
[TD]

10,35​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

jjj​

[/TD]
[TD]

22:28​

[/TD]
[TD]

23:24​

[/TD]
[TD]

22,47​

[/TD]
[TD]

23,40​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

jjj​

[/TD]
[TD]

22:27​

[/TD]
[TD]

12:28​

[/TD]
[TD]

22,45​

[/TD]
[TD]

36,47​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

jjj​

[/TD]
[TD]

13:33​

[/TD]
[TD]

14:18​

[/TD]
[TD]

13,55​

[/TD]
[TD]

14,30​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

jjj​

[/TD]
[TD]

15:59​

[/TD]
[TD]

17:20​

[/TD]
[TD]

15,98​

[/TD]
[TD]

17,33​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

jjj​

[/TD]
[TD]

11:48​

[/TD]
[TD]

12:28​

[/TD]
[TD]

11,80​

[/TD]
[TD]

12,47​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD]

jjj​

[/TD]
[TD]

10:47​

[/TD]
[TD]

11:37​

[/TD]
[TD]

10,78​

[/TD]
[TD]

11,62​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[TD]

jjj​

[/TD]
[TD]

13:59​

[/TD]
[TD]

15:00​

[/TD]
[TD]

13,98​

[/TD]
[TD]

15,00​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

28​

[/TD]
[TD]

jjj​

[/TD]
[TD]

16:38​

[/TD]
[TD]

18:00​

[/TD]
[TD]

16,63​

[/TD]
[TD]

18,00​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

29​

[/TD]
[TD]

jjj​

[/TD]
[TD]

22:04​

[/TD]
[TD]

07:55​

[/TD]
[TD]

22,07​

[/TD]
[TD]

31,92​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

30​

[/TD]
[TD]jjj[/TD]
[TD]

11:13​

[/TD]
[TD]

11:58​

[/TD]
[TD]

11,22​

[/TD]
[TD]

11,97​

[/TD]
[TD]

FALSE​

[/TD]
[TD]

4​

[/TD]
[/TR]
</tbody>[/TABLE]


Helper1
Formula in D2 copied down
=B2*24
Format as Number with 2 decimal places

Helper2
Formula in E2 copied down
=(C2+(B2>C2))*24
Format as Number with 2 decimal places

Formula in G2 copied down
=IF(F2=FALSE,SUMPRODUCT(--(ROW(A$2:A$400)<>ROW(A2)),--(A$2:A$400=A2),--(A$2:A$400<>""),--(F$2:F$400=FALSE),1-((D2>E$2:E$400)+(D$2:D$400>E2))),0)

M.

 

[yessir]

It worked great, if i might ask. What was your reasoning when creating the helper columns.

 

[Marcelo Branco]

It worked great, if i might ask. What was your reasoning when creating the helper columns.

To adjust the value of column C by adding 1 (24 hours) because it is on the next day when it is less than the value of column B.

M.