Split multiple dates values find MAX

mchilapur · Jun 15th 2016

Dear all,
I have a column with multiple data values in each cell.
Count of date values varies from 1 to 7 max.
I need a code to retain only maximum of these date values in each cell and delete rest.

Please note: Date values are separated with single spaces and colon as well.

2016/05/25
2016/05/25,2016/05/25
2016/05/25 2016/05/25
2016/05/25 2016/05/25
2016/05/25 2016/05/25
2016/05/24 2016/05/24 2016/05/24 2016/06/07,2016/06/07
2016/04/28 2016/04/28 2016/05/12
2016/04/28,2016/04/28
2016/04/28,2016/04/28
2016/04/28 2016/04/28

Please help to improve my logic in these kind of cases.
Thanks

skywriter · Jun 15th 2016

Re: Split mutiple dates values in each cell and retain larger value only

How about a small sample sheet with all the possible separators.

[sw]*[/sw]

mchilapur · Jun 15th 2016

Re: Split mutiple dates values in each cell and retain larger value only

Please find the attachment..

skywriter · Jun 15th 2016

Re: Split mutiple dates values in each cell and retain larger value only

Here's the code and there's an attached file. :cool:

Code

Sub MaxDates()
Dim c As Long, j As Long
Dim k, x, tempDate As Date
With Worksheets("Sheet1")
    x = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
For j = LBound(x) To UBound(x)
    If x(j, 1) <> vbNullString Then
        k = Split(Replace(Trim(x(j, 1)), " ", ","), ",")
            tempDate = vbEmpty
        For c = LBound(k) To UBound(k)
            If CDate(k(c)) > tempDate Then tempDate = CDate(k(c))
        Next c
        If tempDate <> vbEmpty Then x(j, 1) = Format(tempDate, "m/d/yyyy")
    End If
Next j
Range("B2").Resize(UBound(x), 1).Value = x
End Sub

Display More

mchilapur · Jun 15th 2016

Re: Split mutiple dates values in each cell and retain larger value only

Thanks for your code...Works like a charm...And great logic too....:):nono:

skywriter · Jun 15th 2016

Re: Split multiple dates values find MAX

My pleasure, thanks for the feedback. :cool:

Split multiple dates values find MAX

Participate now!

Share