Change Multiple date formats to one format

Novice

Board Regular
Joined
Mar 18, 2003
Messages
53
Hi All,
I have been given a spreadsheet that has been updated by a number of different users over time. The problem is the date format used by some is different then others.
I would like to have all the dates in the format DD-MON-YYYY if possible.
I tried the text to column option and 2009 May 01 would come out as 20 September 2008
after I selected DMY.
Is there any VB code that can look at each cell in a column, interperate its correct date and put it in the DD-MON-YYYY format if it is not that already?
Thanks
Novice
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi Novice,

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. Whatever you do, be careful with dates where the day # is less than 13.

Code:
Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim 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
    oCell.Value = Mid(oTxt, 2, .WorksheetFunction.Find("/", oTxt) - 2) & "/" & _
    Mid(oTxt, .WorksheetFunction.Find("/", oTxt) + 1, .WorksheetFunction.Find("/", _
    .WorksheetFunction.Replace(oTxt, .WorksheetFunction.Find("/", oTxt), 1, " ")) - _
    .WorksheetFunction.Find("/", oTxt) - 1) & "/" & Right(oTxt, Len(oTxt) - _
    .WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
    .WorksheetFunction.Find("/", oTxt), 1, " ")))
  Next oCell
End With
End Sub

Cheers
PS: You should have posted this question in the Excel forum.
 
Upvote 0
Thanks for your help.
I will post any others in the excel forum.
Thanks Again
Novice
 
Upvote 0

Forum statistics

Threads
1,215,373
Messages
6,124,555
Members
449,170
Latest member
Gkiller

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top