0

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.

Improve this question
4
  • You need to use Regular Expression to tell Excel which is day number and year number. Here is relative answer to your question: stackoverflow.com/a/7106594/516245 Commented Sep 30, 2013 at 7:47
  • 1
    The real description of the problem seems to be that the cell data is not a date, but a string. Commented Sep 30, 2013 at 9: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"? Commented Sep 30, 2013 at 9:55
  • iDevlop is correct. simply put a date variable wherein you can pass the cell value. then return that variable as the cell value. Commented Oct 1, 2013 at 7:07

2 Answers 2

Reset to default
1

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.

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

Comments

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.

Improve this answer

2 Comments

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(...
I updated my question a bit, I have tried this didn't work or something else is wrong.

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.