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

[cob98tp]

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!

 

[MarkAndrews]

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]

Just use edit and replace.

Edit "." and replace with "/"

 

[btadams]

here's a macro solution:

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]

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