REPLACE function with dates

[Riikka]

My problem is about the REPLACE function. I'm creating macros, and there's a range of cells with dates in format dd/mm/yyyy (or dd.mm.yyyy). I should convert these to ddmmyyyy! Win XP doesn't let me use that date format directly (saw in Regional settings).

If I use REPLACE, so that "/" would replaced with "" (none), the result somehow is dd/mm/yy and not ddmmyyyy as it should be! I've tried formatting the cells to text, or custom, first, but it doesn't help. If I format the cells to text and then manually remove the /'s, that works, but it's not really useful, because I'm creating macros, and I should have a function which I could use there. Hope there's someone there who can help me...

 

[Andrew Poulsom]

Try this:

Sub Test()
    If InStr(1, Selection.NumberFormat, "/") Then
        Selection.NumberFormat = "ddmmyyyy"
    End If
End Sub

For some reason on my machine (Win2000, Excel2000) formatting a date as dd/mm/yyyy results in a format of m/d/yyyy (per the Macro recorder), but it displays correctly.

So when I used:

If Selection.NumberFormat = "dd/mm/yyyy" Then

nothing happened. Strange?