none
Finding Oldest Date from Multiple Columns including Null RRS feed

  • Question

  • I have a huge number of date columns (some of which are null), I need to find the oldest date amongst them and returned in a new column.

    col 1     |       col 2      |        col 3    | col 4            |       col n

    9/1/1996 | 7/1/1998 | 8/26/2002 |                     | 10/9/2008

    8/2/2008 | 7/8/2009 | 6/25/2001 | 9/1/2009     | 1/9/2010

    Result should be like

    col n+1

    9/1/1996

    6/25/2001

    Monday, July 27, 2015 8:09 PM

Answers

  • I would use a custom VBA function:

    Public Function MaximumDate(ParamArray AValues() As Variant) As Variant
    
      Dim Count As Long
      Dim HasResult As Boolean
      Dim Result As Date
      
      HasResult = False
      Result = #1/1/1900#
      For Count = 0 To UBound(AValues())
        If IsDate(AValues(Count)) Then
          HasResult = True
          If CDate(AValues(Count)) > Result Then
            Result = CDate(AValues(Count))
          End If
        End If
      Next Count
      
      If HasResult Then
        MaximumDate = Result
      Else
        MaximumDate = Null
      End If
    
    End Function
    Use it as =MaximumDate([col1],..,[colX]).
    • Proposed as answer by André Santo Monday, July 27, 2015 8:33 PM
    • Marked as answer by L.HlModerator Wednesday, August 5, 2015 8:48 AM
    Monday, July 27, 2015 8:18 PM

All replies

  • Hi,

    If that is an Access table, then you might reconsider its structure. It would be easier to get what you want if the dates were in one column rather than in multiple columns.

    In any case, one approach to get what you want with the structure you have might be something like this custom GetMaxArray() function.

    Hope that helps...

    Monday, July 27, 2015 8:13 PM
  • I would use a custom VBA function:

    Public Function MaximumDate(ParamArray AValues() As Variant) As Variant
    
      Dim Count As Long
      Dim HasResult As Boolean
      Dim Result As Date
      
      HasResult = False
      Result = #1/1/1900#
      For Count = 0 To UBound(AValues())
        If IsDate(AValues(Count)) Then
          HasResult = True
          If CDate(AValues(Count)) > Result Then
            Result = CDate(AValues(Count))
          End If
        End If
      Next Count
      
      If HasResult Then
        MaximumDate = Result
      Else
        MaximumDate = Null
      End If
    
    End Function
    Use it as =MaximumDate([col1],..,[colX]).
    • Proposed as answer by André Santo Monday, July 27, 2015 8:33 PM
    • Marked as answer by L.HlModerator Wednesday, August 5, 2015 8:48 AM
    Monday, July 27, 2015 8:18 PM