How to change two types of date format in a column to single one in excel?

Question

I have an excel file where there are two types of date format exists in a date column. Like:

1/31/10
02/01/2010

I want a single unique format like - MM/dd/yyyy (02/01/2010). How to achieve it? So far Couldn't do by using DateValue(cellID) function because of the first format (1/31/10) not supporting and also failed by formatting the whole column.

Answer 1

First put this User Defined function in a standard module:

Option Explicit
Public Function zDate(rng As Range) As Date
    Dim arr, yr As String
    arr = Split(rng.Text, "/")
    If Len(arr(2)) = 2 Then
        yr = "20" & arr(2)
    Else
        yr = arr(2)
    End If

    zDate = DateSerial(CLng(yr), CLng(arr(0)), CLng(arr(1)))
End Function

With data in column A (assumed to be in month/day/year order), in B1 enter:

=zdate(A1)

and then apply the desired format to column B: