[SalesForce] Convert String to Date in Formula Field (and then get the YEAR)

In our project, we have this Date-Key (which is a TEXT FIELD) in which we input dates with the ff. format:

dd-MM-yyyy

MM-yyyy

yyyy

Now in our custom object, we also have a formula(text) field that will retrieve the YEAR from the text field above.

The formula that I created is (I still don't know how to get the Year):

TEXT( DATEVALUE(Date_Key__c) )

I was hoping it would work but I only get #Error! in my Formula field.

Example:

Date_Key__c = 19-1-2016

formula(text) = #Error!

I was hoping that I would get 19-1-2016 and from their also retrieve the year which is 2016.

Any help is appreciated. Thanks.

Best Answer

According to the help docs you will need your format to be:

DATEVALUE( "YYYY-MM-DD" )  

You can also convert text to a Date so you can use the string value with your other Date fields and formulas. You’ll want your text to be formatted as “YYYY-MM-DD”...

If you are only needing the year from the existing text then you can use the RIGHT function described here.

RIGHT(Date_Key__c, 4)
Related Topic