Change date formats using a macro 01.23.11

**SAP_hostage** · 01-23-2011, 01:58 PM

Hi: I need to change date formats in imported report data. They are lists of dates in columns punctuated by full stops (or periods). The macro I found replaces . with / which works, but messes up any numeric data using decimal places, turning them into date formats.

I know I can use find and replace to do this, but as every report needs this attention I assume a macro would be faster, when several columns might be affected, any help would be greatly appreciated. Thank you.

**DonkeyOte** · 01-23-2011, 02:26 PM

Presumably these period delimited dates are presently showing as Text strings rather than as true Dates (numbers) ?

Sub Example()
    Dim rngData As Range
    With Sheets("Sheet1")
        On Error Resume Next
        Set rngData = .UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
        On Error GoTo 0
        If Not rngData Is Nothing Then
            Intersect(rngData, .Range("A:A,C:C,F:G")).Replace ".", "/"
        End If
        Set rngData = Nothing
    End With
End Sub

modify to suit but in principle above would adjust text strings within columns A:C, F & G on Sheet1 replacing . with /
(there are restrictions regards number of areas specialcells can work with)

**SAP_hostage** · 01-23-2011, 03:04 PM

Thank you that is brilliant! Should I be able to choose the column labels via an input message box, if I work on it, or is there an even better way of doing the same thing, without affecting the numeric data. I am only querying this I as think that find and replace on multiple columns works pretty quickly too? Thanks again.

**DonkeyOte** · 01-23-2011, 03:10 PM

Originally Posted by SAP_hostage

Thank you that is brilliant! Should I be able to choose the column labels via an input message box, if I work on it, or is there an even better way of doing the same thing

You could use Selection or Application.InputBox (type:=8) if you prefer to select the ranges manually at run time.

Originally Posted by SAP_hostage

...without affecting the numeric data.

As stated numbers are ignored (even if they reside within the selected range)

**SAP_hostage** · 01-31-2011, 03:58 PM

I have found a problem with this macro in practice as dates such 01.05.2011 are returned as 05/01/2011 and vice versa 05.01.2011 would return as 01/05/2011. Is there something thn can cure this problem please, it is that annoying American format, I am guessing?

**DonkeyOte** · 01-31-2011, 04:17 PM

Can you post a sample file with a variety of dates in place as you receive them ?

Also could you please confirm your Regional Settings - as specified in Windows Control Panel.