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
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.
