I have 3 different fields that track year in the form of text (4 characters). I'm trying to create a new formula field that would give me the latest year out of all 3 fields.

For example: field1= 1999, field2=2002, field3=2011, the formula should return value 2011. All fields are text right now so it might need to be converted.

Any help is appreciated!

Max(Value(Field1), Value(Field2), Value(Field3))

Max returns the largest value of its parameters, Value converts text to numbers.

Edit: The previous function only works if all three fields are numbers and populated. If this won't always be true, use the following version:

  If(IsNumber(Field1__c), Value(Field1__c), 0), 
  If(IsNumber(Field2__c), Value(Field2__c), 0), 
  If(IsNumber(Field3__c), Value(Field3__c), 0)

This one will always show a value even if one or more fields are empty.

| improve this answer | |
  • This formula makes perfect sense and no errors show with "Check Syntax". However the field stays empty for whatever reason. Any ideas what could be the issue? Thanks! – user1781482 Mar 22 '15 at 14:55
  • @user1781482 You had some blank values present. I've provided an updated function that ignores blank values. – sfdcfox Mar 22 '15 at 15:17

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.