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

Question

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?

do you need the date and time or just date? – Siddharth Rout Apr 13 '16 at 05:44 — Siddharth Rout, Apr 13 '16 at 05:44

score 2 · Answer 1 · edited May 23 '17 at 10:28

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

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

edited May 23 '17 at 10:28

Community

1
1

answered Apr 13 '16 at 08:49

Siddharth Rout

147,039
17
206
250

score 1 · Answer 2 · answered Apr 13 '16 at 06:20

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

Vegard · Accepted Answer · 2016-04-13T07:53:42.627

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

edited Apr 13 '16 at 07:53

answered Apr 13 '16 at 06:31

Vegard

3,587
2
22
40

If the syntax is the same, you can iterate over the data and apply the same algorithm. – Vegard Apr 13 '16 at 06:34
what if im going to use this method to remove 2 or 3 more columns? how can i do it? – 7A65726F Apr 13 '16 at 07:12
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. – Vegard Apr 13 '16 at 07:21
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. – Vegard Apr 13 '16 at 07:22
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... – 7A65726F Apr 13 '16 at 07:26
See updated answer for an example on how to do this. But we're venturing off-topic for this question. If you need help with iteration, you should post a new question. – Vegard Apr 13 '16 at 07:54
Preface it with `If r.Value <> ""` – Vegard Apr 15 '16 at 09:06

score 0 · Answer 4 · answered Apr 13 '16 at 06:03

0

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

answered Apr 13 '16 at 06:03

INOPIAE

293
1
8

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

4 Answers4