Time Band Max problem

[Peter1973]

Sorry about the title not sure what to put for this one.

I am trying to resurect an old problem.

I need a formula to look in a range of data in column A
This will hold data in hh:mm ddd format and the data details the times vehicles leave site.
Then look in column B which again will house data in hh:mm format, this details the time the vehciles arrive back on site.

My probleb is I need the formula to calculate what is the maximum number of vehicles in use at any one time ie off site.

I can do this with lots of seperate data ie table upon table but am trying to get this done in the most efficient way without lots of data (am hoping excel can do this).

The data will only ever cross over 2 days ie 00:01 Fri to 23:59 Sat so no need to worry about several days impacting this.

If anyone has any ideas on this let me know or if you need me to provide some data and results.

Or tell me if i need to stick to the numerous tables currently used to calculate this.

Any help appreciated

 

[CWatts]

Is your source data text, or is a serial date/time just displayed in the hh:mm ddd format?

If that was a case and it was serial, you could use a helper column (C) and test if the departure time is less than your test time and if the return time is greater than your test time. That would indicate that vehicle is out., you could then count column C and that will tell you how many vehicles are out at that time.

If they're stored as text, you can convert them to serial numbers and go from there.

 

[Dryver14]

In column B do you have scheduled return time or does it remain blank until a vehicle returns?

 

[Peter1973]

Cwatts soundS like your going with what I currently do ie with a large table of data calculating the vehicle off site at that specific time rather then just being able to give me the max figure needed at any time.

Dryver every vehcle has a return time they will never be blank.

Also my data is text so think this one may be a step to far for ahwt can be done.

 

[CWatts]

I think I'm misunderstanding something. Is there something about that that method that isn't working out?

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:71px;" /><col style="width:71px;" /><col style="width:45px;" /><col style="width:24px;" /><col style="width:71px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">Leave</td><td style="text-align:center; ">Return</td><td style="text-align:center; ">Status</td><td > </td><td >Date/Time</td><td style="text-align:center; ">Count</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">05:50 Wed</td><td style="text-align:center; ">13:50 Thu</td><td style="text-align:center; ">Out</td><td > </td><td style="text-align:center; ">11:20 Wed</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">08:32 Sun</td><td style="text-align:center; ">06:52 Mon</td><td style="text-align:center; ">In</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(AND<span style=' color:008000; '>(A2<$E$2,B2>$E$2)</span>,"Out","In")</td></tr><tr><td >F2</td><td >=COUNTIF(C:C,"Out")</td></tr><tr><td >C3</td><td >=IF(AND<span style=' color:008000; '>(A3<$E$2,B3>$E$2)</span>,"Out","In")</td></tr></table></td></tr></table>

 

[ZVI]

Let’s assume that A2:A20 is the range for leaving time of each vehicle, and B2:B20 is for the arriving time.
Then try this array formula (use Ctrl-Shift-Enter instead of Enter) for calculating the maximum number of vehicles in use at any time:
=MAX(MMULT((TRANSPOSE(A2:A20)<=A2:A20)*(TRANSPOSE(B2:B20)>=A2:A20),ROW(A2:A20)^0))

 

[ZVI]

Instead of the formula solution the VBA UDF shown below works correctly for any data layouts.
Copy this code into VBA module:

Function MaxWorkedCount(LeaveTime As Range, ArriveTime As Range) As Long
  Dim a&(), i&, r&, x&, t1, t2
  t1 = LeaveTime
  t2 = ArriveTime
  If Not IsArray(t1) Then MaxWorkedCount = 1: Exit Function
  ReDim a(1 To UBound(t1))
  For r = 1 To UBound(t1)
    For i = 1 To r
      a(r) = a(r) - CLng(t2(i, 1) > t1(r, 1))
    Next
    x = -CLng(a(r) > x) * a(r) - x * CLng(a(r) <= x)
  Next
  MaxWorkedCount = x
End Function

And for the data layout described in post #6 put the formula into the cell: =MaxWorkedCount(A2:A20;B2:B20)

 

[Peter1973]

Thnaks for the help but cant seem to get this working, Cwatts the issue with your method is I would need to have a table with every possible time in in order to find what time of day has the most number of vehicles out at any one time.

ZV1 your formula doesnt seem to work I have used the following :
=MAX(MMULT((TRANSPOSE(B2:B10)<=B2:B10)*(TRANSPOSE(C2:C10)>=B2:B10),ROW(B2:B10)^0))

like the table below and the formula returns 0 when it should return 9.

Think i will need to stick to the table format as not sure if this can be done in a single formula.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl63 height=19 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>B</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR>

<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2666556 class=xl66 height=19>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=19>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=19>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>19:00 Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>03:00 Sat</TD></TR></TBODY></TABLE>

 

[ZVI]

Please compare the result of my testing with the similar data:

Excel Workbook
	A	B	C	D	E
1	Leave	Return		UDF	Array formula
2	19:00 Sat	03:00 Sun		9	9
3	19:00 Sat	03:00 Sun
4	19:00 Sat	03:00 Sun
5	19:00 Sat	03:00 Sun
6	19:00 Sat	03:00 Sun
7	19:00 Sat	03:00 Sun
8	19:00 Sat	03:00 Sun
9	19:00 Sat	03:00 Sun
10	19:00 Sat	03:00 Sun
Sheet

The type of data in A:B columns should be the time, not a string (text) type