sort dates

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have a list of 100 dates (some are duplicate) - how do I find the 5 most
recent unique dates?
Many thanks,
Dan
 
Dan,

For example, in cell C1:

=MAX(A1:A100)

In cell C2, array enter (enter using Ctrl-Shift-Enter)
=MAX(($A$1:$A$1000<C1)*$A$1:$A$1000)

and then copy C2 to C3:C5.

HTH,
Bernie
MS Excel MVP
 
Dan,

I just realized that you are in programming....

Try the code below.

HTH,
Bernie
MS Excel MVP


Sub Find5LatestDates()
Dim Latest(1 To 5) As Date
Dim myC As Range
Dim i As Integer
Dim j As Integer

For Each myC In Range("A1:A100")
For i = 1 To 5
If myC.Value = Latest(i) Then GoTo NextDate
Next i
For i = 1 To 5
If myC.Value > Latest(i) Then
For j = 4 To i Step -1
Latest(j + 1) = Latest(j)
Next j
Latest(i) = myC.Value
GoTo NextDate
End If
Next i
NextDate:
Next myC

For i = 1 To 5
MsgBox Format(Latest(i), "mm/dd/yyyy")
Next i

End Sub
 
Back
Top