Sheet formula to calculate maximum gap between dates

[arpd123]

Hi all. I have the following problem:

I have a table and for each record there are multiple date fields. Here is a brief example (these dates use the British format of Day/Month/Year):

[TABLE="width: 658"]
<tbody>[TR]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[TD]Date4[/TD]
[TD]Date5[/TD]
[TD]Date6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/09/2017[/TD]
[/TR]
[TR]
[TD="align: right"]03/11/2017[/TD]
[TD="align: right"]06/10/2017[/TD]
[TD="align: right"]28/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13/11/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]26/04/2018[/TD]
[TD="align: right"]03/04/2018[/TD]
[TD="align: right"]02/05/2018[/TD]
[TD][/TD]
[TD="align: right"]02/05/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20/04/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/02/2018[/TD]
[TD="align: right"]05/02/2018[/TD]
[TD][/TD]
[TD="align: right"]06/03/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/12/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/09/2017[/TD]
[TD][/TD]
[TD="align: right"]19/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]03/05/2018[/TD]
[TD][/TD]
[TD="align: right"]26/04/2018[/TD]
[TD="align: right"]06/04/2018[/TD]
[TD="align: right"]02/05/2018[/TD]
[TD="align: right"]02/05/2018[/TD]
[/TR]
[TR]
[TD="align: right"]23/04/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/04/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/09/2017[/TD]
[TD][/TD]
[TD="align: right"]02/04/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]26/04/2018[/TD]
[TD="align: right"]03/04/2018[/TD]
[TD="align: right"]02/05/2018[/TD]
[TD][/TD]
[TD="align: right"]02/05/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[TD="align: right"]18/10/2016[/TD]
[/TR]
</tbody>[/TABLE]

I would like a sheet formula which will enable me to find the size of the largest gap in "consecutive" dates in each record.

As you can see, the data are entered haphazardly. e.g. it cannot be assumed that Date2 is after Date1. It may in fact precede it. If Date1 is not filled, it does not mean that a subsequent date field is not also filled. The formula has to remove blanks from consideration.

Taking the example of the second row in the above data, the largest gap is between Date1 and Date2 (28 days), and this is the required answer. Between Date2 and Date 3 in the same row it is 8 days. The gap between Date1 and Date3 is larger at 36 days, but does not count because Date2 is intermediate between them. You can assume that the header Date1 is in cell A1.

Thanks for any help on this.

 

[Chameleon64]

I'm not sure if your example is representative of your full data so I've assumed you stop at Date 6 (the formula would get unwieldy beyond that). For row 3 the formula would be:

=MAX(IF(AND(A3>0,B3>0),ABS(A3-B3),0),IF(AND(B3>0,C3>0),ABS(B3-C3),0),IF(AND(C3>0,D3>0),ABS(C3-D3),0),IF(AND(D3>0,E3>0),ABS(D3-E3),0),IF(AND(E3>0,F3>0),ABS(E3-F3),0))

Use of ABS means dates can be before or after each other.

 

[arpd123]

I'm not sure if your example is representative of your full data so I've assumed you stop at Date 6 (the formula would get unwieldy beyond that). For row 3 the formula would be:

=MAX(IF(AND(A3>0,B3>0),ABS(A3-B3),0),IF(AND(B3>0,C3>0),ABS(B3-C3),0),IF(AND(C3>0,D3>0),ABS(C3-D3),0),IF(AND(D3>0,E3>0),ABS(D3-E3),0),IF(AND(E3>0,F3>0),ABS(E3-F3),0))

Use of ABS means dates can be before or after each other.

Thank you for the response. However, it is not giving the correct answer in general as you seem to have assumed that the dates Date1-Date6 are ordered chronologically in either direction, but I am afraid they are not.

I thought about the comparisons route, but it is as you say rather unwieldy. I think you have to consider all fifteen possible comparisons in each row, and eliminate at least ten of those, depending on how many values are actually filled in each row, and whether the two values being compared have an intermediate value between them elsewhere in the row.

I was wondering if there is a technique with array formulas to work on an intermediate, sorted array of values in the range A3:F3, say.

 

[Chameleon64]

The formula only needs to check consecutive dates in my interpretation of what you wanted. As I stated, I've included the ABS function so that the chronological order doesn't matter. The results I get from the formula I developed are as follows:

[TABLE="width: 115"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

If these aren't the answers you're expecting, please advise the correct ones and it may help to clarify your question.

 

[arpd123]

The formula only needs to check consecutive dates in my interpretation of what you wanted. As I stated, I've included the ABS function so that the chronological order doesn't matter. The results I get from the formula I developed are as follows:

If these aren't the answers you're expecting, please advise the correct ones and it may help to clarify your question.

I can see that there is some ambiguity, my apologies. I do not mean by "consecutive" that e.g. Date1 and Date2 are necessarily consecutive. I mean instead the harder problem of finding the biggest gap if the dates in each row were to be arranged chronologically.

 

[Chameleon64]

OK, I think I see where you're coming from now. In that case I think it's too complicated with standard Excel functions and you would be better off writing a custom VBA function to loop through all the permutations.

 

[Chameleon64]

If you're allowed to use VBA then this should do the trick. If not then it may help someone else

Usage example: =MaxDateDifference(A10:F10)

Function MaxDateDifference(rng As Range)

Dim i As Long

Dim cell As Range
Dim myArray() As Long
Dim DateDifference As Long

' Resize array to match number of items in selected range
ReDim myArray(0 To rng.Count - 1)

' Populate array
i = 0
For Each cell In rng
    myArray(i) = cell
    i = i + 1
Next cell

' Sort array into date order
Call QuickSort(myArray, LBound(myArray), UBound(myArray))

' Identify largest date difference in array
DateDifference = 0

For j = 1 To UBound(myArray)
    If myArray(j) - myArray(j - 1) > DateDifference And myArray(j - 1) > 0 Then
        DateDifference = myArray(j) - myArray(j - 1)
    End If
Next j

MaxDateDifference = DateDifference

End Function

Sub QuickSort(arr, Lo As Long, Hi As Long)
  Dim varPivot As Variant
  Dim varTmp As Variant
  Dim tmpLow As Long
  Dim tmpHi As Long
  tmpLow = Lo
  tmpHi = Hi
  varPivot = arr((Lo + Hi) \ 2)
  Do While tmpLow <= tmpHi
    Do While arr(tmpLow) < varPivot And tmpLow < Hi
      tmpLow = tmpLow + 1
    Loop
    Do While varPivot < arr(tmpHi) And tmpHi > Lo
      tmpHi = tmpHi - 1
    Loop
    If tmpLow <= tmpHi Then
      varTmp = arr(tmpLow)
      arr(tmpLow) = arr(tmpHi)
      arr(tmpHi) = varTmp
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop
  If Lo < tmpHi Then QuickSort arr, Lo, tmpHi
  If tmpLow < Hi Then QuickSort arr, tmpLow, Hi
End Sub