Quick Date Conversion Question - dd.mm.yyyy to dd/mm/yyyy

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Hi All,

I have a list with about 11,000 dates in the format dd.mm.yyyy which is not recognised as a date by Excel. I need to come up with a formula to make this into a date as I'm not keen on changing all 11,000 by hand!

Thanks!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Format > Cells > Number (tab)> Custom dd/mm/yyyy

(Select the cell range first - Column A etc)

EDIT - dates like 25.09.2006 opposed to 25/09/2006
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
Just use edit and replace.

Edit "." and replace with "/"
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
here's a macro solution:

Code:
Sub ConvertEuroDates()
Din cell as Range
Dim strDay As String, strMonth As String, strYear As String
Dim intP1 As Integer, intP2 As Integer, intResponse as Integer

'   Abort if a range isn't selected
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells", vbOKOnly + vbInformation, "Invalid Range Selection"
        Exit Sub
    End If
'    On Error GoTo exitsub
    intResponse = MsgBox("Click Yes to convert Euro dates (dd.mm.yy) to US dates (mm/dd/yy)" & vbCrLf & "Click No to convert US dates to Euro dates" & vbCrLf & "Click Cancel to exit", vbYesNoCancel, "Convert Dates")
    If intResponse = vbCancel Then Exit Sub
    Application.ScreenUpdating = False
    If intResponse = vbYes Then
        Application.Calculation = xlCalculationManual
        For Each Cell In Selection
            On Error Resume Next
            intP1 = Application.WorksheetFunction.Find(".", Cell.Value)
            If Err <> 0 Then
                strDay = Application.WorksheetFunction.Substitute(Left(Cell.Text, 2), "/", "")
                strMonth = Application.WorksheetFunction.Substitute(Mid(Cell.Text, 4, 2), "/", "")
                strYear = Application.WorksheetFunction.Substitute(Right(Cell.Text, 2), "/", "")
                If strMonth > 12 Or strDay > 31 Then
                    'Do Nothing
                Else
                    Cell.Value = strDay & "/" & strMonth & "/" & strYear
                End If
                On Error GoTo 0
                GoTo NextCell
            End If
            intP2 = Application.WorksheetFunction.Find(".", Cell.Value, intP1 + 1)
            strDay = Left(Cell.Text, intP1 - 1)
            strMonth = Mid(Cell.Text, intP1 + 1, intP2 - intP1 - 1)
            strYear = Right(Cell.Text, Len(Cell.Text) - intP2)
            If CInt(strMonth) > 12 Or CInt(strDay) > 31 Then
                MsgBox "The value in cell " & Cell.Address & " (" & Cell.Value & ") cannot be converted"
                GoTo NextCell
            End If
            
            Cell.Value = strMonth & "/" & strDay & "/" & strYear
NextCell:
        On Error GoTo 0
        Next Cell
        ActiveCell.EntireColumn.AutoFit
    End If
    If intResponse = vbNo Then
        Selection.NumberFormat = "mm/dd/yy"
        For Each Cell In Selection
            strMonth = Left(Cell.Text, 2)
            strDay = Mid(Cell.Text, 4, 2)
            strYear = Right(Cell.Text, 2)
            Cell.Value = strDay & "." & strMonth & "." & strYear
        Next Cell
        ActiveCell.EntireColumn.AutoFit
    End If
exitsub:
    If Err <> 0 Then
    MsgBox "An Error Occurred: " & Err.Number & " " & Err.Description, vbOKOnly + vbExclamation, "File Open Error"
    On Local Error GoTo 0
    End If
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
 

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Thanks all, wish I'd thought of Edit Replace... In the end I used...

=IF(ISTEXT(B2),DATE(RIGHT(B2,2)+100,MID(B2,4,2),LEFT(B2,2)),B2)

as some of the dates were ok, and some with .'s
 

Forum statistics

Threads
1,137,335
Messages
5,680,887
Members
419,937
Latest member
Talic

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
Top