1

I have a generated file coming from a system the date format of this file is like this Mar 28 2016 4:55:54:380PM my script in VBA doesn't recognize this as date or rather Excel doesn't recognize this as a date format but only a string. Is there other way around?

Improve this question
2
  • do you need the date and time or just date? Commented Apr 13, 2016 at 5:44
  • i just need the date.. Commented Apr 13, 2016 at 6:06

4 Answers 4

Reset to default
2

Here is a 2 line code ;)

I am assuming that the range is from A1:A20. Please amend as applicable

Sub Sample()
    [A1:A20].NumberFormat = "DD/MM/YYYY"
    [A1:A20] = [index(DATE(MID(A1:A20,8,4),MONTH(1&LEFT(A1:A20,3)),MID(A1:A20,5,2)),)]
End Sub

enter image description here

If you want to understand what this code does then see the explanation that I have given Here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You mentioned in a comment that you need just the date:

Sub dateTest()
    Dim d As Date
    s = "Mar 28 2016 4:55:54:380PM"
    s = Left(s, 11)
    d = DateSerial(Year(s), Month(s), Day(s))
    Debug.Print d
End Sub

28.03.2016

To iterate over some dataset:

Sub dateIteration()
    Dim d As Date, v As Variant
    Dim rng As Range

    Set rng = Range("A1:A10")

    For Each r In rng
        v = Left(r.Value, 11)
        d = DateSerial(Year(v), Month(v), Day(v))

        ' Do something with d
        ' Print it to worksheet, maybe?
        r.Value = d
    Next r
End Sub

To iterate over non-contiguous ranges with minimal code clutter:

Sub helperSub()
    Call dateIteration(Range("A1:A10"))
    Call dateIteration(Range("Z1:Z10"))
    Call dateIteration(Range("H1:M89"))
End Sub

Sub dateIteration(rng As Range)
    Dim d As Date, v As Variant

    For Each r In rng
        v = Left(r.Value, 11)
        d = DateSerial(Year(v), Month(v), Day(v))

        ' Do something with d
        ' Print it to worksheet, maybe?
        r.Value = d
    Next r
End Sub
Improve this answer

7 Comments

If the syntax is the same, you can iterate over the data and apply the same algorithm.
what if im going to use this method to remove 2 or 3 more columns? how can i do it?
To iterate over several columns? Just define those columns as part of the range. Set rng = Range("A1:C10") and it will run the macro on all cells within that range.
Alteratively, you can define the macro to take a Range as argument and use a helper sub to run the macro several times on each individual range. This will be more useful if you aren't working with a contiguous range.
what i did is use another foor loop for the other column though it's feels like repetitive, column from H, K L, Z random columns...
|
1

try this

Public Function stringtodate(mytext) As Date
    str1 = Split(mytext, " ")
    str2 = UBound(str1)
    If str2 > 2 Then
        If LCase(Left(str1(0), 3)) = "mar" Then
            mon = "03"
        End If
        stringtodate = str1(1) & "-" & mon & "-" & str1(2)
    Else
        'not a valid date
    End If
End Function

enter image description here

Improve this answer

Comments

0

Isn't the Date format within VBA #MM/DD/YYYY h:mm:ss.sss PM#?

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.