changing date format

[bobkasterop]

Hey, so i have to change the date format in an excel sheet from yy-mm-dd to dd-mm-yy.
i tried using numberformat like this 'Range("A2", "A50000").NumberFormat = "dd-mm-yy"' but this doesnt change anything.
i also tried using format(Date,"dd-mm-yy") but this just sets the cell to have the date of today (from my computer)
i think that the reason for numberformat not working is that Excel doesnt really know which of the bits is yy which is dd and which is mm and therefore is thinking that they are allready in the right order.

my question is therefore:
how do i make excel now that right now it is in the order of yy-mm-dd? so that i aftwerwards can use numberformat.

 

[Scott T]

Is your date an Excel date?

Change A2 to where your date is. Does this return TRUE?

=ISNUMBER(A2)

 

[mole999]

if you do have dd mm yy problems then dd mmm yyyy is unmistakable

 

[JoeMo]

Assuming your data are in A2:A50000 (change range to suit):

Sub ChangeDateFormat()
'Assumes initial format is yy-mm-dd and changes it to dd-mm-yy
Application.ScreenUpdating = False
Columns("B").Insert
With Range("B2:B50000")
    .Formula = "=RIGHT(A2,2)&""-""& Mid(A2, 4, 2) & ""-""&LEFT(A2,2)"
    .Value = .Value
End With
Columns("A").Delete
Application.ScreenUpdating = True
End Sub

 

[bobkasterop]

Is your date an Excel date?

Change A2 to where your date is. Does this return TRUE?

=ISNUMBER(A2)

This returns FALSE

 

[bobkasterop]

this does not work for me. it doesnt execute the formula it just turns the value of all the b cells to this "=RIGHT(A2,2)&""-""& Mid(A2, 4, 2) & ""-""&LEFT(A2,2)"

 

[JoeMo]

Your data are not dates (numbers) they are strings. Did you try the macro in post #4 ?

 

[bobkasterop]

yes but it doesnt work for me. it just inserts this "=RIGHT(A2,2)&""-""& Mid(A2, 4, 2) & ""-""&LEFT(A2,2)" directly into the cells instead of executing the formula.

 

[JoeMo]

this does not work for me. it doesnt execute the formula it just turns the value of all the b cells to this "=RIGHT(A2,2)&""-""& Mid(A2, 4, 2) & ""-""&LEFT(A2,2)"

I assume you are addressing me. That's happening because your column B is formatted as text. Here's a revision that may overcome that.

Sub ChangeDateFormat()
'Assumes initial format is yy-mm-dd and changes it to dd-mm-yy
Application.ScreenUpdating = False
With Columns("B")
    .Insert
    .NumberFormat = "General"
End With
With Range("B2:B50000")
    .Formula = "=RIGHT(A2,2)&""-""& Mid(A2, 4, 2) & ""-""&LEFT(A2,2)"
    .Value = .Value
End With
Columns("A").Delete
Application.ScreenUpdating = True
End Sub

 

[bobkasterop]

This just inserts a new row in D and inserts "=RIGHT(A2,2)&""-""& Mid(A2, 4, 2) & ""-""&LEFT(A2,2)" into all the cells in that row...