Extract minimal (oldest) date from comma-separated string of dates from cell-CodePudding

I have a cell with comma-separated date values like

27.02.2022, 09.02.2022, 09.02.2022, 26.02.2022, 13.02.2022, 17.02.2022, 16.02.2022, 27.02.2022, 21.11.2022, 14.11.2022, 25.12.2022, 02.03.2022, 02.03.2022, 04.04.2022, 02.03.2022, 07.02.2022, 02.03.2022, 17.11.2022, 04.04.2022, 23.02.2022, 25.02.2022, 28.02.2022, 01.03.2022, 03.02.2022, 28.11.2022, 16.02.2022, 02.02.2022, 01.03.2022, 31.03.2022, 17.02.2022, 05.11.2022, 20.02.2022, 02.02.2022, 13.02.2022, 16.02.2022, 29.09.2022, 16.02.2022, 16.02.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 24.12.2022, 02.02.2022, 02.02.2022, 09.02.2022, 09.02.2022, 09.02.2022, 26.12.2022, 19.02.2022, 25.02.2022, 15.02.2022, 02.02.2022, 03.02.2022, 18.02.2022, 23.12.2022, 02.02.2022, 02.02.2022, 10.02.2022, 02.02.2022, 02.02.2022, 02.02.2022, 02.02.2022, 20.02.2022, 17.02.2022, 20.02.2022, 12.03.2022, 18.02.2022, 02.02.2022, 02.02.2022, 05.11.2022, 21.11.2022, 23.11.2022, 09.02.2022, 28.11.2022, 01.03.2022, 01.03.2022, 02.03.2022, 04.04.2022, 12.02.2022, 12.02.2022, 01.03.2022, 01.03.2022, 11.02.2022, 23.02.2022, 16.02.2022, 28.02.2022, 16.02.2022, 16.02.2022, 02.02.2022, 02.02.2022, 02.02.2022, 02.02.2022, 02.02.2022, 02.02.2022, 09.11.2022, 28.02.2022, 13.02.2022, 13.02.2022, 03.02.2022, 03.02.2022, 03.02.2022, 03.02.2022, 03.02.2022, 03.02.2022, 02.03.2022, 02.03.2022, 21.11.2022, 12.02.2022, 17.02.2022, 23.02.2022, 02.03.2022, 02.03.2022, 02.03.2022, 21.11.2022, 10.02.2022, 10.02.2022, 03.02.2022, 18.03.2022, 18.12.2022, 18.12.2022, 02.02.2022, 17.11.2022, 28.02.2022, 02.02.2022, 02.02.2022, 31.03.2022, 02.02.2022, 28.02.2022, 16.02.2022, 16.02.2022, 16.02.2022, 17.11.2022, 02.03.2022, 24.10.2022, 09.11.2022, 21.02.2022, 01.03.2022, 17.02.2022, 21.11.2022, 11.11.2022, 17.02.2022, 03.03.2022, 16.02.2022, 16.02.2022, 02.03.2022, 23.11.2022, 02.02.2022, 27.02.2022, 26.02.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 02.03.2022, 31.03.2022, 28.11.2022, 24.12.2022, 24.12.2022, 03.02.2022, 03.04.2022, 15.02.2022, 22.02.2022, 01.03.2022, 01.03.2022, 28.02.2022, 16.02.2022, 16.02.2022, 16.02.2022, 19.02.2022, 01.03.2022, 01.03.2022, 20.02.2022, 20.02.2022, 20.02.2022, 20.02.2022, 04.02.2022, 19.02.2022, 13.02.2022, 13.02.2022, 20.02.2022, 20.02.2022, 28.02.2022, 17.11.2022, 16.02.2022, 16.02.2022, 09.11.2022, 13.02.2022, 28.02.2022, 28.02.2022, 28.02.2022, 28.02.2022, 21.11.2022, 10.02.2022, 28.11.2022, 05.11.2022, 23.12.2022, 25.02.2022, 17.02.2022, 25.02.2022, 25.02.2022, 12.03.2022, 12.03.2022, 23.12.2022, 26.12.2022, 16.11.2022, 16.02.2022, 16.02.2022, 16.02.2022, 07.02.2022, 16.02.2022, 07.02.2022, 07.02.2022, 28.02.2022, 01.03.2022, 07.02.2022, 25.12.2022, 28.02.2022, 28.02.2022, 28.02.2022, 28.02.2022, 23.02.2022, 05.11.2022, 01.03.2022, 01.03.2022, 14.02.2022, 21.11.2022, 16.02.2022, 16.02.2022, 16.02.2022, 16.02.2022, 13.02.2022, 09.02.2022, 28.02.2022, 13.02.2022, 23.02.2022, 15.02.2022, 16.02.2022, 16.02.2022, 16.02.2022, 21.02.2022, 21.02.2022, 05.02.2022, 28.02.2022, 24.10.2022, 04.04.2022, 04.04.2022, 16.02.2022, 29.09.2022, 09.02.2022, 09.02.2022, 09.02.2022, 24.12.2022, 22.02.2022, 22.02.2022, 14.02.2022, 18.03.2022, 18.12.2022, 29.09.2022, 23.11.2022, 14.11.2022, 03.02.2022, 03.02.2022, 03.02.2022, 03.02.2022, 03.02.2022, 13.02.2022, 28.11.2022, 21.11.2022, 21.02.2022, 05.11.2022, 25.02.2022, 13.02.2022, 06.02.2022, 23.12.2022, 15.02.2022, 22.02.2022, 22.02.2022, 10.02.2022, 07.02.2022, 28.02.2022, 28.02.2022, 28.02.2022, 18.12.2022, 15.02.2022, 24.12.2022, 17.11.2022, 12.02.2022, 12.03.2022, 12.03.2022

I need to get the oldest date value to separate cell. Tried to modify this solution:

=MIN(IFERROR(DATEVALUE(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(A1) -LEN(SUBSTITUTE(A1,",","")) 1))-1)*999 1,999))),0))

found here. But it seems, I have too many dates in my cell, and changing 999 to ,say, 9999 didn't help.

Im getting 0 as a result in the target cell. But on shorter cells solution does work.

Any help will be very much appreciated. Formula or VBA function needed. Thank you!

CodePudding user response：

If you are using ms365 with acces to the newest functions you could try:

=MIN(--TEXTSPLIT(A1,", "))

If your office doesn't recognize the date-pattern you may need to substitute the dots:

=MIN(--TEXTSPLIT(SUBSTITUTE(A1,".","-"),,", "))

If you don't yet have TEXTSPLIT() then try FILTERXML():

=MIN(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,".","-"),", ","</s><s>")&"</s></t>","//s"))

Or, if you don't have ms365 (but Excel 2013 or higher) and you don't want to use CSE then use xpath itself.

CodePudding user response：

Please, test the next VBA function, working in any Excel versions:

Function ExtractOldestDate(strD As String) As Date
    Dim arrD: arrD = Split(strD, ", ")
    Dim i As Long, oldD As Date, arr, compD As Date
    
    oldD = DateSerial(2050, 1, 1)
    For i = 0 To UBound(arrD)
        arr = Split(arrD(i), "."): compD = DateSerial(CLng(arr(2)), CLng(arr(1)), CLng(arr(0)))
        If compD < oldD Then oldD = compD
    Next
    ExtractOldestDate = oldD
End Function

The above function will process in that way a series of (Date) strings in format "dd.mm.yyyy", or "d.m.yyyy". In order to process a different string format, DateSerial should be adapted accordingly. I basically mean the used separator and day/month/year position in that string...

It may be tested in VBA, using the next testing Sub:

Sub testExtrOldD()
  Dim strD As String
  
  strD = Range("A1").value
  Debug.Print ExtractOldestDate(strD)
End Sub

Of as UDF, called from a cell as:

  =ExtractOldestDate(A1)

The cell keeping the formula, must be formatted as Date...

CodePudding user response：

If your current Windows default date format does not match the format of the dates in your list then you would probably need to split them into day, month and year to convert them reliably into numeric dates:

Public Function ExtractOldestDateFromStringList(ByVal ListOfDateStrings As String, ByVal Delimiter As String, ByVal DateFormat As String) As Variant
    Dim ArrayOfDateStrings() As String
    ArrayOfDateStrings = Split(ListOfDateStrings, Delimiter)
    
    Dim OldestDate As Date
    
    Dim DateString As Variant
    For Each DateString In ArrayOfDateStrings
        Dim NumericDate As Variant
        NumericDate = ConvertStringToDate(DateString, DateFormat)
        
        If Not IsError(NumericDate) Then
            If CDate(NumericDate) < OldestDate Or OldestDate = 0 Then
                OldestDate = CDate(NumericDate)
            End If
        Else
            ' Something went wrong
            ExtractOldestDateFromStringList = NumericDate
            Exit Function
        End If
    Next DateString
    
    ExtractOldestDateFromStringList = OldestDate
End Function


Public Function ConvertStringToDate(ByVal DateString As String, ByVal DateFormat As String) As Variant
' returns #VALUE! if something is wrong with the DateString
' returns #N/A if the DateFormat is not supported

    Dim Delimiter As String
    If InStr(DateString, "/") Then
        Delimiter = "/"
    ElseIf InStr(DateString, ".") Then
        Delimiter = "."
    ElseIf InStr(DateString, " ") Then
        Delimiter = " "
    ElseIf InStr(DateString, "-") Then
        Delimiter = "-"
    Else
        ' no valid delimiter found
        ConvertStringToDate = CVErr(xlErrValue)
        Exit Function ' return #VALUE!
    End If
        
    ' split into parts
    Dim SplitString() As String
    SplitString = Split(DateString, Delimiter)
    
    Dim NumericDate As Date
    
    ' convert to numeric date
    Select Case DateFormat
        'day month year
        Case "dd.mm.yyyy", "d.m.yyyy", "dd.mm.yy", "d.m.yy", "dd/mm/yyyy", "d/m/yyyy", "dd/mm/yy"
            NumericDate = DateSerial(SplitString(2), SplitString(1), SplitString(0))
        
        'month day year
        Case "mm/dd/yyyy", "m/d/yyyy", "mm/dd/yy"
            NumericDate = DateSerial(SplitString(2), SplitString(0), SplitString(1))
        
        'year month day
        Case "yyyy-mm-dd", "yyyy/m/d", "yyyy/mm/dd"
            NumericDate = DateSerial(SplitString(0), SplitString(1), SplitString(2))
        
        Case Else
            ConvertStringToDate = CVErr(xlErrNA)
            Exit Function ' return #N/A
    End Select
    
    ' check conversion if it is a real date and matches the string
    If Format$(NumericDate, Replace$(DateFormat, Delimiter, "\" & Delimiter)) = DateString Then
        ConvertStringToDate = NumericDate
    Else
        ConvertStringToDate = CVErr(xlErrValue)
        ' return #VALUE!
    End If
End Function

This can be used as formula like:

=ExtractOldestDateFromStringList(A1,", ","dd.mm.yyyy")