0

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
Improve this question
1
  • please research what the With command does. you are using it in an absolutely incorrect way, and it will get you into trouble in the future. Commented Aug 24, 2017 at 17:25

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

perfect! worked!! Thanks! and one more question if its possible. I want to vlookup this date in another sheet but the date in another sheet is in format "dd/mm/yyyy hh:mm". Is it possible to still vlookup ignoring the hh:mm?
Hmm, tricky, depends on the exact conditions. Maybe, if you can add a helper column in the other sheet with a formula like =int(A2) (removes the time part)
a ok ! I will try! And just one last thing with the code you pasted above.. it works great for specific dates but I forgot to mention before that the date that the code should convert each time is an input form the user, which is saved as a variable named myValue.. So, I tried to add this variable instead of Sheets("1").Range("B1").Value but just wont work as the myValue is a variable dimed two times. Anyway, I have edited the code just in case you can help!
ok sorry, I fixed the problem, and it runs but instead of dd/mm/yyyy it appears to be mm/dd/yyyy when the dd<12 ... so, now when I type the date 12/01/2017 it appears as 01/12/2017 but when I type 13/01/2017 it is printed correctly...
Have you removed the lines that write the user input (MyValue) to the range before the string handling? If you write the input to the cell and the reread it, Excel will try to convert the input as a date and it has it's own mind about that
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.