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

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

CodePudding user response：

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.

CodePudding user response：

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