[RESOLVED] Trying to sort a list into date order.

[Poppa Mintin]

Hi,

I have functioning project which I'm trying to modify to display a list of times achieved, recorded with the relevant date, into date order.

The list already exists as a List of String, named 'times' currently with 28 items.
This is the method I thought I'd use:

Private Sub SortDate()
        Dim txt As String
        Dim count As Integer = times.Count - 1
        Dim order As New SortedList(Of Date, String)
 
        For i = 0 To count
            txt = Date.Parse(times(i).Substring(0, 12).Trim)
            txt = txt.Substring(0, 8)
            order.Add(txt & i.ToString, " " & times(i))
        Next
 
        TextBox2.Clear()
        For i = 0 To count
            txt = order(i).Substring(10)
            TextBox2.AppendText(txt & NewLine)
        Next

I had to add the i.ToString in Line 9 to change the key names, after that all works up to line 12. Achieving 28 items in the 'order' list.

When I come to add lines 13 to 16, intellisense complains about the index in line 14:

Severity Code Description Project File Line Suppression State
Error BC30311 Value of type 'Integer' cannot be converted to 'Date'. SudokuRecord D:\Visual Studio Projects\Projects\SudokuRecord\Form1.vb 111 Active

So clearly I'm not using the correct code to read the list.
I've tried adding the index to the line in the list that I'm trying to read but can't find how to do that.


Poppa

[jmcilhinney]

It's slightly frustrating that you have asked how to sort a list of Strings based on a date contained in those Strings without actually showing us what those Strings look like. Whatever you do, it depends on correctly parsing the text to a Date but we can't address that if we don't know the format of the Strings.

Ignoring that for the moment, I would suggest either of the following options:

1. Sorting the list in place:

myList.Sort(Function(s1, s2) CDate(s1).CompareTo(CDate(s2)))

2. Creating a new list:

myList = myList.OrderBy(Function(s) CDate(s)).ToList()

You just need to replace the CDate in each case with the appropriate code to parse your text, which we can't help you with.

[Poppa Mintin]

Thanks John,

Yes, you're right I omitted a sample string, it was nearly 2 AM and I forgot... That's my story and I'm sticking to it.

I couldn't figure out how to use either of your suggestions, but in the cool light of day I resorted to a simple 'Bubble' sort.
The first item in the 'times' list is "00: 17: 19. 10th. September 2020".
I want this to be the last item in the sorted list. By the time the string is ready to be sorted it would look like this:
"10/09/20 00: 17: 19. 10th. September 2020", this gives a constant format from which to extract a date without changing the original text.
(I've changed the name of the sorted list from 'order' to 'dated', I think it makes it more readable.)

    Private Sub SortDate()
        Dim num As Integer = times.Count - 1
        Dim spt(), txt As String
        Dim dated As New List(Of String), dat1, dat2 As Date

        For i = 0 To num
            txt = times(i).Substring(12).Trim
            spt = Split(txt, ".")                ' Remove the 'st, nd  or th'.
            txt = Val(spt(0)).ToString & spt(1)  ' This can be the 2nd or 3rd character.
            txt = Date.Parse(txt)
            txt = txt & "  " & times(i)
            dated.Add(txt)
        Next
        For one = 0 To num - 1
            For two = one + 1 To num
                dat1 = Date.Parse(dated(one).Substring(0, 8))
                dat2 = Date.Parse(dated(two).Substring(0, 8))
                dat = DateTime.Compare(dat1, dat2)
                If dat > -1 Then
                    txt = dated(two)
                    dated(two) = dated(one)
                    dated(one) = txt
                End If
            Next
        Next
        TextBox2.Clear()
        For i = 0 To num
            TextBox2.AppendText(dated(i).Substring(8).Trim & NewLine)
        Next
    End Sub

Poppa

PS.
I've just noticed that I've not declared 'dat', how have I got away with that ?
Pop

PPS.
Yes I have, it's a global variable.

Pop

[jmcilhinney]

If the first eight characters of each String represent a date in dd/MM/yy format and that's what you want to sort by then, as i said, you simply replace the CDate with code that will convert that substring to a Date, i.e.

myList.Sort(Function(s1, s2) Date.ParseExact(s1.Substring(0, 8), "dd/MM/yy", Nothing).CompareTo(Date.ParseExact(s2.Substring(0, 8), "dd/MM/yy", Nothing)))

The second option is more efficient because the above will perform the Substring and ParseExact for twice for every comparison, which could be a lot for a large list, while the alternative will perform them once for each item.

[Poppa Mintin]

Thanks again John,

That's answered the question.

It's a neater method than my last working subroutine.

    Private Sub SortDate()
        Dim dated As New List(Of String), spt(), txt As String

	' Make  New List(Of String) 'dated' 
	' Copy  List(Of String) 'times' and extract date.
	' For each item: Start with extracted date, add original string.
        ' Add item to 'dated'.

        For i = 0 To times.Count - 1
            txt = times(i).Substring(12).Trim
            spt = Split(txt, ".")
            txt = Val(spt(0)).ToString & spt(1)
            txt = Date.Parse(txt)
            txt = txt & "  " & times(i)
            dated.Add(txt)
        Next

	' Sort List(Of String) 'dated' into date order.

        dated.Sort(Function(s1, s2) Date.ParseExact(s1.Substring(0, 8), "dd/MM/yy", Nothing) _
                        .CompareTo(Date.ParseExact(s2.Substring(0, 8), "dd/MM/yy", Nothing)))

	' Display list in TextBox2, ensuring that TextBox2 scrolls down to the last entry.

        TextBox2.Clear()
        For i = 0 To dated.Count - 1
            TextBox2.AppendText(dated(i).Substring(8).Trim & NewLine)
        Next
    End Sub

Problem solved.


Poppa