worksheet function - Multiple date formats in single column in excel

I have a csv file with a date column with dates in various forms as follows:

dd-mm-yyyy

yyyy-mm-dd

yyyy-mm

yyyy

This irregular data is in a single columns.

How do I convert all of them to the dd-mm-yyyy and extract the year from them using excel formula? The desired output is as follows:

Input       Output
25-03-1954  25-03-1954
22-09-1987  22-09-1987
1990-01-25  25-01-1990
1968-11-15  15-11-1968
1919-01     01-01-1919
1873-02     01-02-1873
1945        01-01-1945
1933        01-01-1933

Answer

If all that you really need is the Year, then use @robinCTS formula.

IF you want the result to be "real Excel Dates" and

• if your data is TEXT and not real dates,


• and if your regional date settings are DMY,


• and the column is formatted as dd-mm-yyyy

then you could use this formula:

=IFERROR(IFERROR(IFERROR(DATEVALUE(A2),DATEVALUE(A2&"-01")),DATEVALUE(A2&"-01-01")),"illegal date format")

Note that dates prior to 1900 cannot be real dates in Excel, but could be represented as TEXT strings. I will leave it to you to tweak the formula.

If your output needs to be internationally aware to countries that have other date formats, then a VBA solution might be best. If that is not possible, helper columns to split the data and then reassemble appropriately, or a very complex formula to do the same, could be used.

Note that this UDF will output either the actual date, or just the year, depending on the second optional argument.

Option Explicit
Function ConvertToDate(S As String, Optional Yr As Boolean = False) As Variant
    Dim V
    Dim dtTemp As Date
V = Split(S, "-")
Select Case UBound(V)
    Case 0
        dtTemp = DateSerial(V(0), 1, 1)
    Case 1
        dtTemp = DateSerial(V(0), V(1), 1)
    Case 2
        If Len(V(0)) = 4 Then
            dtTemp = CDate(S)
        Else
            dtTemp = DateSerial(V(2), V(1), V(0))
        End If
End Select
If Yr = True Then
    ConvertToDate = Year(dtTemp)
Else
    If Year(dtTemp) < 1900 Then
        ConvertToDate = Format(dtTemp, "dd-mm-yyyy")
    Else
        ConvertToDate = dtTemp
    End If
End If
End Function

Both methods look at the first segment, and assume it is a year if LEN > 4, and then look at how many segments to decide whether to append the necessary -01's

The VBA solution will output dates prior to 1900 as a text string.