Converting non-standard date format (e.g., "5 Sep") to dd/mm (e.g., 05/09)

I have a VB.NET problem, whereby I want to convert non-standard dates in the header row of an Excel spreadsheet into a different format. Specifically, the worksheet arrives with an alphanumeric string variable day and month (e.g., "5 Sep") but I need it convert that to another string as "05/09".

Unfortunately, I neither create these worksheets nor define myself what the output format should be - so stuck between a rock and a hard place. Any help would be greatly appreciated as I've wasted two days getting nowhere so far! Many thanks in advance.

                Try
                    xlWorkBook = xlApp.Workbooks.Open(result2)
                    xlWorkSheet = xlWorkBook.Worksheets("Attendances")

                    xlWorkBook.Activate()
                    If xlWorkSheet.Cells(1, 1).Value = "Course" Then
                        xlWorkSheet.Rows(1 & ":" & 3).Delete()
                        xlWorkBook.Save()



                        For t As Integer = 6 To 75
                            If xlWorkSheet.Cells(1, t).Value <> "" And Len(xlWorkSheet.Cells(1, t).Value) > 25 Then
                                xlWorkSheet.Cells(1, t).Value = Microsoft.VisualBasic.Left(xlWorkSheet.Cells(1, t).Value, InStr(xlWorkSheet.Cells(1, t).Value, " 20") - 1)
                            End If
                        Next                           

                        xlWorkBook.Save()
                        xlWorkBook.Close()
                    End If
                    xlApp.Quit()
                Catch ex As Exception
                    lblPath.Text = "Exception"
                End Try

2 answers

  • answered 2021-09-27 16:30 Andrew Morton

    If you retain the year part, you can use the DateTime.Parse function. Then you can convert the resulting DateTime to a string with only the parts you want.

    For t As Integer = 6 To 75
        Dim v = xlWorkSheet.Cells(1, t).Value
        If (Not String.IsNullOrWhiteSpace(v)) AndAlso Len(v) > 25 Then
            Dim sd = String.Join(" ", v.Split({" "c}, 4).Take(3))
            Dim d = DateTime.Parse(sd, New Globalization.CultureInfo("en-GB"))
            xlWorkSheet.Cells(1, t).Value = $"=""{d.ToString("dd/MM")}"""
        End If
    Next
    

    The value is of the form ="05/09" so that Excel doesn't format it like a date and doesn't do the division. Maybe you don't need to force it like that.

    I suggest that the original data be kept somewhere, perhaps by hiding the column, and a new column is used for the display.

  • answered 2021-09-27 17:12 UnhandledException-InvalidChar

    Maybe this helps you:

    Private Function GetMonthNumber%(s$)
    
    Select Case UCase(Trim(s$))
        Case "JAN"
         Return 1
        Case "FEB"
         Return 2
        '...
        Case Else
        'bad argument
        Return 0 
    End Select
    End Function
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum