CODEWITHSUNDEEP
vbaexceldate
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Date from Text format to Date format in VBA

I want to change dates from text format to date format (custom) dd/mm/yyyy hh:mm . I have been reading all types of similar questions inside the website but nothing seems to work for me. Even if I apply changes, the date stays in a text format. Is there a way to use the Date function in VBA. Or generally, any ideas about how I can finally make it work. My dates are vlookups from an excel sheet named "TMS", where they are in a text format. The destination sheet is "Tracker". The dates are imported from a website to the "TMS" sheet so I have to perform the change in format automatically inside the excel. My code is provided below. Much appreciated!!

The code below is the fixed code, for which the date format worked, but it does not run the loop for every row, instead it just copy paste the value of the first row to the other rows. In other words, it works perfectly for the first row, but not for the other!

Sub Tracker()

 Sheets("TMS").Select
lastrow = Range("B" & Rows.Count).End(xlUp).Row
With Range("G2:G" & lastrow)
If Not IsEmpty(Range("G2:G" & lastrow)) Then
.value = .Parent.Evaluate("DATE(MID(" & .Address & ",7,4),MID(" & .Address & ",4,2),LEFT(" & .Address & ",2))+RIGHT(" & .Address & ",4)")
End If
End With
Sheets("Tracker").Select
lastrow = Range("B" & Rows.Count).End(xlUp).Row
With Range("AG2:AG" & lastrow)
.Formula = "=VLOOKUP(B2,TMS!B:G,6,FALSE)"
.value = .value
End With

End Sub

Upvotes: 2

Views: 2677

Answers (4)

Dirk Reichel
Dirk Reichel

Reputation: 7979

simply add to your With Range("G2:G" & lastrow) part:

.Value = .Parent.Evaluate("DATE(MID(" & .Address & ",7,4),MID(" & .Address & ",4,2),LEFT(" & .Address & ",2))+RIGHT(" & .Address & ",4)")

this should change all strings to numerical values in one step :)

EDIT
As Evaluate does not want to return an array this way, we simply force it via INDEX:

.Value = .Parent.Evaluate("INDEX(DATE(MID(" & .Address & ",7,4),MID(" & .Address & ",4,2),LEFT(" & .Address & ",2))+RIGHT(" & .Address & ",4),)")

Upvotes: 3

Dy.Lee
Dy.Lee

Reputation: 7567

Try this code.

Sub Tracker()
    Dim vDB, vT, vD
    Dim i As Long
    With Sheets("TMS")
        lastrow = .Range("B" & Rows.Count).End(xlUp).Row
        With .Range("G2:G" & lastrow)
            vDB = .Value
            If IsDate(vDB(1, 1)) Then
            Else
                For i = 1 To UBound(vDB, 1)
                    vT = Split(vDB(i, 1), " ")
                    vD = Split(vT(0), "/")
                    vDB(i, 1) = DateSerial(vD(2), vD(1), vD(0)) + Val(Trim(vT(1)))
                Next i
            End If
            .Value = vDB
            .NumberFormat = "mm/dd/yyyy hh:mm"
        End With
    End With
    With Sheets("Tracker")
        lastrow = .Range("B" & Rows.Count).End(xlUp).Row
        With .Range("AG2:AG" & lastrow)
            .Formula = "=VLOOKUP(B2,TMS!B:G,6,FALSE)"
            .NumberFormat = "mm/dd/yyyy hh:mm"
            .Value = .Value
        End With
    End With
End Sub

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

POC

in the image above, I have illustrated the formulas used to convert from text to their various components then back to a date serial including time. The format for F2 was set as a custom format to display correctly.

Upvotes: 3

Tehscript
Tehscript

Reputation: 2556

I am not sure how your worksheet is organised but considering the dates are imported to Sheets("TMS").Range("G2:G" & lastrow), and you are not able to change their format by using only .NumberFormat = "mm/dd/yyyy hh:mm" then you need to get rid of the complete text and paste them as dates.

You should also avoid selecting sheets. Your code should look something similar to this. Please correct the parts if I guess them incorrectly.

Sub Tracker()
Dim lastrow As Long
Dim arr() As Date
With Sheets("TMS")
    lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
    ReDim arr(lastrow) As Date
    For i = 2 To lastrow
        arr(i) = .Range("G" & i).Value
    Next i

    .Range("G2:G" & lastrow).Delete
    For i = 2 To lastrow
        .Range("G" & i) = arr(i)
    Next i
    .Range("G2:G" & lastrow).NumberFormat = "mm/dd/yyyy hh:mm"
End With
With Sheets("Tracker")
    lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
    With .Range("AG2:AG" & lastrow)
        .Formula = "=VLOOKUP(B2,TMS!B:G,6,FALSE)"
        .NumberFormat = "mm/dd/yyyy hh:mm"
    End With
End With
End Sub

Upvotes: 0

Related Questions