CODEWITHSUNDEEP
excelvbadate
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Conversion of dates from text format to date format works partially

I am trying to convert a date from text format to date format. The code I have provided below works great for all dates that have as dd a number bigger than 12 .So for dd=13 to dd=31 it works fine. When I have a date with dd<13 then it just not work. the date I am providing below is an example of a date that the code wont work. It leaves the cell B1 empty. Also, my system settings are set for dd/mm/yyyy so its ok with the system. anyone knows why it does not work?

Sheets("1").Select
Dim myValue As Variant
myValue = InputBox("Simi, type the date of the shipments that you want to create the report for", "Date", 1)
Sheets("1").Range("B1").Value = myValue
With Sheets("1").Range("B1")
Dim t() As String
t = Split(myValue, "/")  ' Split into pieces
If UBound(t) = 2 Then ' If 3 elements found:
myValue = DateSerial(t(2), t(1), t(0))  ' Convert to Date
End If
End With

Upvotes: 0

Views: 77

Answers (1)

FunThomas
FunThomas

Reputation: 29592

To be honest, it's a little hard to understand you code - you are somehow dealing with Excel formulas within VBA - no need for that.

Try something like

   Dim r As Range
    Set r = ThisWorkbook.Sheets(1).Range("B1")
    r.Value = "08/01/2017"
    With r
        Dim s As String, t() As String   

        s = r.Value    ' Read cell content into string
        t = Split(s, "/")  ' Split into pieces 
        If UBound(t) = 2 Then ' If 3 elements found:
            r.Value = DateSerial(t(2), t(1), t(0))  ' Convert to Date
        End If
    End With

Version with user input (no need to write it to the cell before converting it)

    Dim myValue As Variant
    myValue = InputBox("Simi, type the date of the shipments that you want to create the report for", "Date", 1)
    Dim r As Range
    Set r = ThisWorkbook.Sheets(1).Range("B1")
    With r
        Dim s As String, t() As String   

        s = myValue   ' Use user input
        t = Split(s, "/")  ' Split into pieces 
        If UBound(t) = 2 Then ' If 3 elements found:
            r.Value = DateSerial(t(2), t(1), t(0))  ' Convert to Date
        End If
    End With

Upvotes: 1

Related Questions