2

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
Improve this question
10
  • your dates are most likely stored as text not a number. Assuming you have a date in B6 try =istext(b6) if it returns true, then you will need to convert it to a serial date which excel uses. once that is done you can apply your custom format to it and use date functions on it. Commented Aug 19, 2017 at 23:55
  • it is a text ! how can I convert it to a serial date? Commented Aug 19, 2017 at 23:58
  • Copy the values to an array, delete the content, change cells' format as date, paste the content back from the array, and they are all dates. Commented Aug 20, 2017 at 0:05
  • use the built in text to columns is one way and tell the prompt that the column is in a certain date format. Alternatively you can read through the text with your VBA and strip id down character group by character group to pull the day, then the month then the year and then use a VBA equivalent to the excel formula date(year, month, day) Commented Aug 20, 2017 at 0:10
  • @Tehscript sorry for being annoying but I really dont know how to do this .. I am really new at vba! can you help me further? Commented Aug 20, 2017 at 0:15

4 Answers 4

Reset to default
3

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

11 Comments

nice one dirk. if only I could write VBA code as easy as I do excel formulas. Love the fact that it is a one line fix.
@ForwardEd I've written it in a cell -> copy to notepad -> replace A1 with " & .Address & " -> just add .Value = .Parent.Evaluate(...) and done... (I AM very lazy) :D
@DirkReichel your trick actually worked well ... however, even if I get the desired format, this order keeps pasting the same value till the last row.. So, instead of having 25 different dates, I have the same date in all 25 cells.
@PericlesFaliagas added an INDEX around the formula to force an array.... should work now :)
@PericlesFaliagas in that case, do the conversation simply for the "tracker" sheet. Do not touch the "TMS" at all and use my line at your ".value = .value" part.
|
3

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.

Improve this answer

3 Comments

I believe the OP is having a different issue. Try inserting '20/08/2017 into a cell, then try changing its format as you have done in your answer. Correct me if I am wrong?
my answers was to give him some tools he could use to rewrite his code to reformat his date in text format to a serial date format that he could then use the remainder of his code to reformat to the custom format. as stated in my comments under the initial question, there is a VBA command that can use Excel formulas. So with out doing all the code writing, I was just supplying portions that may help make the step in the conversion process. @Tehscript
And I am the bad guy again, giving the short solution :P
0

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
Improve this answer

1 Comment

Based on system settings 20/07/2017 01:30 probably can not be interpreted as datetime. Because looking for mm/dd/yyyy will return an error if the month is 20. if that happens, no conversation will happen and it will stay a string. To reenter it into the cell may end with the same result (except for dates where month and day can be misread which will end up with wrong dates)... just keep that in mind ;)
0

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
Improve this answer

Comments

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.