CODEWITHSUNDEEP
vbaexceldatetimedate-format
7A65726F
7A65726F

Reputation: 167

Date information in a format Excel doesn't recognize as a Date

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?

Upvotes: 1

Views: 97

Answers (4)

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

Upvotes: 2

Vegard
Vegard

Reputation: 4917

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

Upvotes: 1

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

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

Upvotes: 1

INOPIAE
INOPIAE

Reputation: 293

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

Upvotes: 0

Related Questions