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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Supplier[/TD]
[TD]Arrival Date & Time[/TD]
[TD]Departure Date & Time[/TD]
[TD]Trailer[/TD]
[TD]4/19/18 01:00:00[/TD]
[TD]4/19/18 02:00:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ASD[/TD]
[TD]4/20/18 0:01:12[/TD]
[TD]4/20/18 16:34:33[/TD]
[TD]A234[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ASD[/TD]
[TD]4/20/18 11:00:52[/TD]
[TD]4/20/18 16:00:00[/TD]
[TD]A235[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BFB[/TD]
[TD]4/19/18 01:05:34[/TD]
[TD]4/22/18 03:05:56[/TD]
[TD]B545[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CCC[/TD]
[TD]4/19/18 05:06:35[/TD]
[TD]4/20/18 03:20:36[/TD]
[TD]C555[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BFB[/TD]
[TD]4/18/18 00:30:15[/TD]
[TD]4/22/18 03:56:45[/TD]
[TD]B546[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]MAX#[/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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