Picking the most recent date?

[smithgt]

Here's a strange problem that I'm trying to resolve.

I import some data from another system that has a column identifying which weeks schedule a particular task is scheduled in.

If the task is not completed then it can roll over into another week.

E.g. on the first row the task is in the 20th June and the 27th June.

What I'm trying to do is just pull out the most recent date and then use that value is my other reports & calculations.

I do know the dates in advance (they are each Monday) and could create a list of potential dates and the identify the most recent in the column - but I'm not sure how to go about this, or if this is the best approach.

Royalties: 20th June 2011 (#4), Royalties: 27th June 2011 (#2)
Royalties: 13th June 2011 (#10), Royalties: 6th June 2011 (#4)
Royalties: E1 Approved For Development (July) (#4)
Royalties: E1 RHG to Spec - Aim September (#4)
Royalties: 23rd May 2011 (Complete) (#4)
Royalties: E1 Approved For Development (Sept) (#4)
Royalties: 25th April 2011 (Complete) (#4), Royalties: 9th May 2011 (Complete) (#3)
Royalties: WREN Release (Complete) (#4)
Royalties: 28th March 2011 (Complete) (#4)
Royalties: E1 Approved For Develoment (August) (#4)
Royalties: 9th May 2011 (Complete) (#4)
Royalties: 2nd May 2011 (Complete) (#4)
Royalties: 18th April 2011 (Complete) (#5), Royalties: 4th April 2011 (Complete) (#4)

Unfortunately, the date order within the column is also not in any set order either

Can anyone suggest a method here?

 

[Sektor]

Is Royalties: 20th June 2011 (#4), Royalties: 27th June 2011 (#2) in one cell?

 

[smithgt]

Yes, but it is comma seperated so I could split them into individal columns.

 

[Sektor]

This UDF will extract all dates from cell. But what to do with them? Currently, dates are returned separated by semicolon.

[COLOR="Blue"]Function[/COLOR] ExtractDates(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] sDates [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], m [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Global = [COLOR="Blue"]True[/COLOR]
        .Pattern = "\d{1,2}(st|nd|rd|th)\s+\w+\s+\d{4}"
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] m [COLOR="Blue"]In[/COLOR] .Execute(Str)
            sDates = sDates & m.Value & "; "
        [COLOR="Blue"]Next[/COLOR]
        ExtractDates = Left(sDates, Len(sDates) - 2)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[smithgt]

I was thinking that I could used a nestED IF, seaching for the most recent dates first - as that really what I want to know.

=IF(ISNUMBER(SEARCH("27th June",I9)),"27th June",IF(ISNUMBER(SEARCH("20th June",I9)),"20th June",IF(ISNUMBER(SEARCH("13th June",I9)),"13th June",IF(ISNUMBER(SEARCH("13th June",I9)),"13th June",I9))))

Is there a better programacical way to do this?

 

[Sektor]

So, let's make picture clearer. You want to take first row, extract all dates in it and return most recent one?

 

[smithgt]

So, let's make picture clearer. You want to take first rows, extract all dates in it and return most recent one?

Yes

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] RecentDate(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    
    [COLOR="Blue"]Dim[/COLOR] m [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] iDay [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], iMonth [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], iYear [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arrMonths [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], arrDates() [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
    arrMonths = VBA.Array("January", "February", "March", "April", _
                          "May", "June", _
                          "July", "August", "September", _
                          "October", "November", "December")
    
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Global = [COLOR="Blue"]True[/COLOR]
        .Pattern = "(\d{2}|\d)(?:st|nd|rd|th)\s+(\w+)\s+(\d{4})"
        [COLOR="Blue"]If[/COLOR] .test(Str) [COLOR="Blue"]Then[/COLOR]
            [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] m [COLOR="Blue"]In[/COLOR] .Execute(Str)
                i = i + 1
                [COLOR="Blue"]ReDim[/COLOR] [COLOR="Blue"]Preserve[/COLOR] arrDates(1 [COLOR="Blue"]To[/COLOR] i)
                iDay = m.SubMatches(0)
                iMonth = WorksheetFunction.Match(m.SubMatches(1), arrMonths)
                iYear = m.SubMatches(2)
                arrDates(i) = [COLOR="Blue"]CLng[/COLOR](DateSerial(iYear, iMonth, iDay))
            [COLOR="Blue"]Next[/COLOR]
            RecentDate = [COLOR="Blue"]CDate[/COLOR](WorksheetFunction.Max(arrDates))
        [COLOR="Blue"]Else[/COLOR]
            RecentDate = "No dates found"
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]