2

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
8
  • What do you mean by conditionally convert? Show the Condition to us ! Jun 3, 2018 at 17:15
  • @RajeshS What I meant by conditional was convertion to dd-mm-yyyy according to the type of input in the cell.
    – Crops
    Jun 3, 2018 at 17:22
  • if simply you want to convert Input dates as the output shows can be dome by TEXT excel Formula. Jun 3, 2018 at 17:41
  • And what do you mean by this,, "and extract the year from them using excel formula?" since your output has all 3 Date, Month & Year !! Jun 3, 2018 at 17:46
  • TEXT can be used individually if I know the format in input. I have thousands of cells of input (in multiple formats) that I want to convert to output. However I am not able to get the output. Once I get the output, I can extract the year using YEAR()
    – Crops
    Jun 3, 2018 at 18:00

2 Answers 2

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.

2
  • OP has never demanded for VBA ,, OP is in need of Excel Formula !! Jun 4, 2018 at 9:33
  • 1
    @RajeshS And that was the first part of the answer. But I also discussed other options. This is not just a free "code for me service" but rather exists to provide information to both the OP and others who may subsequently search the DB for answers. So both explanations of the algorithms, as well as alternative methods to solve a problem, can be useful. Jun 4, 2018 at 9:54
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.