0

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.

Improve this question
1
  • 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
    Commented Apr 11, 2016 at 15:58

1 Answer 1

Reset to default
4

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)
Improve this answer
2
  • 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!
    – hereLiesThisTroper
    Commented 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.
    – Nazeer Ahamed
    Commented Sep 19, 2019 at 9:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .