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.

 

[JoeMo]

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...

I assume you mean inserts a new column in B. Seems you have a stubborn case of cells formatted as text. Here's another shot at beating that problem, and if it doesn't work I'm out of cards to play.

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)"
    .TextToColumns
    .Value = .Value
End With
Columns("A").Delete
Application.ScreenUpdating = True
End Sub