Macro to convert DD/MM/YYYY format to MM/DD/YYYY format

The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in the form of date values that you can format as dates via Format|Cells|Number|Date.

Sub ConvertDateFormat()

Dim DtRange As Range, oCell As Range, oTxt As String

If Selection.Cells.Count = 1 Then

  Set DtRange = ActiveCell

Else

  Set DtRange = Selection

End If

With Application

  On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange

  For Each oCell In DtRange.SpecialCells(xlConstants)

    oTxt = oCell.Text

    If UBound(Split(oTxt, "/")) = 2 Then _

      oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))

  Next oCell

End With

End Sub

Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.

As you'll see, Anreas' claim "if the cells contains dates already, then you are one step to late" isn't right...

If the data is truly text, and not numbers, and if the format is consistently like "Day/Month/Year" as described, then this formula will convert it to an actual date in Excel, which you can then format as you wish.  Assumes your data is in column A starting at row 2.

=DATE(INT(RIGHT(A2,LEN(A2)-FIND("/",A2,FIND("/",A2,1)+1))),INT(MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1)),INT(LEFT(A2,FIND("/",A2,1)-1)))

Sample Results:

HTH,

Eric

In you first post you said you want to convert dates from the format Day/Month/Year to Month/Day/Year, as the topic header shows.

But what you are telling now confuses me totally.

I'm sure that if a cell shows a date in D/M/YYYY and I doesn't work if you apply the format DD/MM/YYYY, then the cell value must be a text.

Make a new file and execute this macro and explore the cells afterwards:

Sub Test()

  Range("A1:A5") = 1234

  Range("A2").NumberFormat = "D/M/YYYY"

  Range("A3").NumberFormat = "DD/MM/YYYY"

  Range("A4").NumberFormat = "MM/DD/YYYY"

  Range("A5").NumberFormat = "YYYY/MM/DD"

End Sub

Important note: Any cell contains 1234 as value!! The numberformat only change Excels interpretation of the value, means the visible result on screen.

I would like to see a sample of your data, the data file before import if possible and an Excel file with the data directly after import and a manually filled column with the dates that you expect instead.

Please look in ym profile for my email address and instructions.

Andreas.

Hello, Andreas,

Actually, this is a report generated from within a document management system which is an SAP product, but runs outside of SAP on our intranet.  The report itself has the date field formatted as such and when I download the report to Excel, it is not the date number that Excel recognizes, it is the format from the report itself.  The report has the format of DD/MM/YYYY but as I stated above, some of the dates in the cell itself are D/MM/YYYY or D/M/YYYY, or DD/M/YYYY but appear on the report as DD/MM/YYYY.  I have tried using formulas to divide it up and format correctly, but they don't work on every line.  Reformatting the column also does not work.  They don't seem to be imported as text; if they were I could work with that.  The month and day names are not included in the date string.

Thanks,

Valerie

Yes, but if the cells contains dates already, then you are one step to late.

As you (should) know a date in Excel is a number. Your SAP exports the data as text, but if you import that text into Excel, it is converted into a number.

And know you have a problem, because it is possible that some of the dates are recognized as "DD/MM/YY" and some as "MM/DD/YY", but all dates are numbers afterwards. So we have no chance to identify which of them came from which format.

But if you import the "dates" as text, I can give you some code to convert a date string into a date, from any format you like.

Are day and/or month names included in your date strings?

Andreas.