Excel not recognizing year when changing to date format with VBA

Question

I'm having trouble changing to date format. I have cells containing different dates but not all of them are formatted as date. One of them is "yy-mm-dd", for example "13-04-08", but it is formatted as General. I'm using this code:

xCell = Format(xCell, "yyyy-mm-dd")

Problem is that excel cannot tell if "13" or "08" is the "yyyy" so it doesn't change anything. How do I solve this? Do I need to tell excel which numbers are year before it changes the date, and how do I do that? Can it be included in the Format method?

EDIT: I think I need to explain the whole thing as the problem seems to lay somewhere else. The cells containing the dates look like this from start, being formatted as General:

13-05-06 A
13-05-21 A
...

I remove the unwanted 'A' with this code:

Sub rensa()

Dim Found As Range

For Each xCell In Range("D2:D999")
    If IsEmpty(xCell.Value) Then Exit For
        Set Found = xCell.Find(What:="A", LookIn:=xlValues, LookAt:=xlPart)
            If Found Is Nothing Then
            Else
                xCell.Value = Left(xCell.Value, Len(xCell.Value) - 1)
            End If
    Next xCell

End Sub

I have tried these codes to set the cell format to date:

Range("D2:D999").NumberFormat = "yyyy-mm-dd"
Range("D2:D999").NumberFormat = "m/d/yyyy"

I have also tried to implement them in the For loop like this:

Sub rensa()

Dim Found As Range

For Each xCell In Range("D2:D999")
If IsEmpty(xCell.Value) Then Exit For
    Set Found = xCell.Find(What:="A", LookIn:=xlValues, LookAt:=xlPart)
        If Found Is Nothing Then
            xCell.NumberFormat = "yyyy-mm-dd"
        Else
            xCell.Value = Left(xCell.Value, Len(xCell.Value) - 1)
            xCell.NumberFormat = "yyyy-mm-dd"
        End If
Next xCell

End Sub

But that didn't work as I wanted either. Everything makes the result look like this, still formatted as General:

13-05-06
13-05-21
...

So the A is gone, but nothing else changes.

You need to use Regular Expression to tell Excel which is day number and year number. Here is relative answer to your question: http://stackoverflow.com/a/7106594/516245 — zur4ik, Sep 30 '13 at 07:47
The real description of the problem seems to be that the cell data is not a date, but a string. — iDevlop, Sep 30 '13 at 09:29
@iDevelop You are right, the cell data is not a date from the beginning. Maybe the title should say "...when changing TO date format"? — andysando, Sep 30 '13 at 09:55
iDevlop is correct. simply put a `date` variable wherein you can pass the cell value. then return that variable as the cell value. — L42, Oct 01 '13 at 07:07

score 1 · Accepted Answer · answered Oct 01 '13 at 06:55

i just added something in your code. See below lines with comments

Sub rensa()

Dim Found As Range
Dim xcell As Range
Dim date_val As Date

For Each xcell In Range("D1:D999")
    If IsEmpty(xcell.Value) Then Exit For
        Set Found = xcell.Find(What:="A", LookIn:=xlValues, LookAt:=xlPart)
            If Found Is Nothing Then
            Else
                xcell.Value = Left(xcell.Value, Len(xcell.Value) - 1)
                date_val = xcell.Value 'asign the value to date variable carrier
                xcell.Value = date_val 'return it to the cell
            End If
Next xcell

Range("D1:D999").NumberFormat = "yyyy-mm-dd" 'do the formatting

End Sub

Hope this works.

Kazimierz Jawor · Answer 2 · 2013-10-01T05:35:17.593

0

Quite simple solution is to parse your data using Mid, Left, Right functions. However, it's not such efficient one would expect but possibly would be helpful. So, the code could be as follows:

xCell = "13-04-08"
xCell = Format(DateSerial(Left(xCell,2),mid(xCell,4,2),Right(xCell,2)),"yyyy-mm-dd")

as a result you get 2013-04-08.

Edit To set appropriate cell formatting try one of the following:

Range("A1").NumberFormat = "yyyy-mm-dd"

or

Range("A1").NumberFormat = "m/d/yyyy"

Where Range("A1") is reference to your cell/range in Excel sheet.

edited Oct 01 '13 at 05:35

answered Sep 30 '13 at 09:19

Kazimierz Jawor

18,861
7
35
55

if you see incorrect output in cell then you need to change `cell formatting` to something like `long date`. In VBA there is `2013-04-08` for sure. Alternatively you could try to add `'` before `format function` in this way: `xCell = "'" & Format(...` – Kazimierz Jawor Sep 30 '13 at 12:11
I updated my question a bit, I have tried this didn't work or something else is wrong. – andysando Oct 01 '13 at 06:12

Excel not recognizing year when changing to date format with VBA

2 Answers2