Formula to determine the Max

[Mbishop]

I'm trying to figure out if there is a more efficient way of calculating the max number of trailers in an area based on arrival and departure. Example below is how i'm currently determining.

	A	B	C	D	E	D
1	Supplier	Arrival Date & Time	Departure Date & Time	Trailer	4/19/18 01:00:00	4/19/18 02:00:00
2	ASD	4/20/18 0:01:12	4/20/18 16:34:33	A234	0	0
3	ASD	4/20/18 11:00:52	4/20/18 16:00:00	A235	0	0
4	BFB	4/19/18 01:05:34	4/22/18 03:05:56	B545	0	1
5	CCC	4/19/18 05:06:35	4/20/18 03:20:36	C555	0	0
6	BFB	4/18/18 00:30:15	4/22/18 03:56:45	B546	1	1
7		MAX#		TOTAL	1	2

<tbody>
</tbody>

In example above I have data of when trailer arrives and departs. I'm using rows E through AA... were I have date with time in hour increments and using using an If formula in cells below those rows =If(E$1>=$B2,if(E$2<=$C2,1,0),0) then summing each column to determine the max peak of trailers in area at that time. Then I have =max(E7:AA...7) were it returns the max trailers.

I would really like to get away from using hourly increments, as Max is not truly accurate, but if I use minutes spread sheet would be more massive than it already is. If possible I would like to input a formula that determines the max so I can get away from adding in the incremental times.

Any considerations and help will be much appreciated.

Thanks

 

[StephenCrump]

Perhaps:

D2: =SUMPRODUCT(--(B$2:B$7<=B2),--(C$2:C$7>B2))
E2: =IF(D2=MyMax,B2,"")
F2: =IF(D2=MyMax,INDEX(C$2:C$7,MATCH(MIN(IF(C$2:C$7>B2,C$2:C$7-B2)),C$2:C$7-B2,)),"") array-entered
MyMax: =D9 =MAX(D2:D7)

Incidentally, I used Sumproduct rather than Countif because Countif seems to be subject to Excel precision errors, e.g. compare:

=COUNTIF(C2:C7,">"&C4) =4, wrong!
=SUMPRODUCT(--(C2:C7>C4)) = 3, correct

Book1
	A	B	C	D	E	F
1	Supplier	Arrive	Depart	Arrive Count	Start	End
2	A	20 Apr 18 2:00:00	20 Apr 18 16:00:00	4	20 Apr 18 2:00	20 Apr 18 16:00
3	B	18 Apr 18 0:30:00	18 Apr 18 15:00:00	1
4	C	19 Apr 18 1:05:00	21 Apr 18 3:05:56	1
5	D	19 Apr 18 2:00:00	21 Apr 18 9:00:00	2
6	E	19 Apr 18 5:06:00	22 Apr 18 3:20:00	3
7	F	21 Apr 18 1:30:00	21 Apr 18 16:00:00	4	21 Apr 18 1:30	21 Apr 18 3:05
8
9			MAX	4
Sheet1