Multiple date formats in single column in excel

Question

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 1

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.

Answer 2

Since your input data contains dates before 1900, things get a little trickier. The simplest way to deal with this is to exclusively work with the text representations of the dates.

If you don't actually need the full date, but just require the year, enter this basic formula in any cell in row 2:2:

=IFERROR(VALUE(LEFT(A2,4)),VALUE(RIGHT(A2,4)))

For the full date, the formula is a bit more complicated:

=IF(MID(A2,3,1)="-",A2,CHOOSE((LEN(A2)-4)/3+1,"01-01","01"&MID(A2,5,3),RIGHT(A2,2)&MID(A2,5,3))&"-"&LEFT(A2,4))

---

Explanation:

The prettified version of the full date formula is as follows:

=
IF(
  MID(A2,3,1)="-",
  A2,
  CHOOSE(
    (LEN(A2)-4)/3+1,
    "01-01",
    "01"&MID(A2,5,3),
    RIGHT(A2,2)&MID(A2,5,3)
  )
  &"-"&LEFT(A2,4)
)

It is pretty straightforward. The only slightly tricky thing is the first argument of the CHOOSE() function, (LEN(A2)-4)/3+1. This maps the lengths of the input date strings to 1-based indexes, i.e., [yyyy, yyyy-mm, yyyy-mm-dd, dd-mm-yyyy] → [4, 7, 10, 10] → [1, 2, 3, 3].

Notes:

• The prettified formula actually works if it is entered.

Caveats:

• The formulas assume that all the input dates are stored as text. (The formulas are easily modifiable to cater for dates stored as serial numbers.)
• If the input dates contain leading/trailing spaces, the above formulas may not work correctly. They will definitely not work with spaces (or other characters) elsewhere in the date strings. Replacing all the A2s in the formulas with TRIM(A2), or better yet with SUBSTITUTE(A2," ",""), will solve this issue.