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




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.


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.

  • If you have a text field, do a string operation on it to remove all but the last four numeric characters which will leave you with the year.
    – crmprogdev
    Apr 11, 2016 at 15:58

1 Answer 1


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


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)
  • 2
    Oh wow this is amazing! Your last suggestion is the thing that I've been looking for! Thank you very much! I'll also change the date format for Date_Key__c to YYYY-MM-DD just in case we need to get the DATEVALUE in the future. For now the LEFT(Date_Key__c, 4) works for us. Again thanks! Apr 12, 2016 at 2:46
  • I get the right result when using below formula in detail page, whereas in the report on related list it shows as "#Error!" - any idea? Formula: DATEVALUE(date1__c ) - DATEVALUE(date2__c). Both fields are text data type containing datetime value. Sep 19, 2019 at 9:37

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.