0

I have 2 text fields, start year & end year, I want to create a formula field that calculates the difference between these two years entered, and if the end year is current, the formula field should return the current year.

IF(
   (ISNULL(VALUE(End_Year__c))),
   VALUE(TEXT(YEAR(TODAY()))), 
   ( VALUE (End_Year__c) -  VALUE(Start_Year__c))
)

When I use the above formula, it returns a number with ' , '. For example: 2,019. I want it in number format without the comma in the formula field.

  • 1
    What have you tried so far? Where are you stuck? You should edit your question to include that (as opposed to replying to me in the comments). – Derek F Sep 4 at 18:17
0

Salesforce by default displays numbers in a locale-specific manner - so in your locale, it's probably N,NNN etc. There is no way to override the display formatting.

What you could do is have your formula field return a text value like your other year fields, which should then return "NNNN" - it will display correctly in the UI, but you would need to convert it to a numeric number value again if you needed to use it elsewhere (or, you could keep the number formula for use in other formulas and create a different formula field for just the display value).

For the first option, you'll need to wrap your values in the TEXT() function. Something like:

IF(
   ISBLANK( End_Year__c ),
   TEXT( YEAR( TODAY() ) ), 
   TEXT( VALUE( End_Year__c ) -  VALUE( Start_Year__c ) )
)

A couple of other notes:

  • ISNULL() is deprecated -- use ISBLANK()
  • You don't need to wrap End_Year__c with VALUE() when you check if it's blank
  • You didn't need to wrap YEAR() with TEXT() and then VALUE() - YEAR() already returns a numeric. But since this example returns a text field we still need that part.
0

You get that ',' because the formula field you have created is of Number return type. To get rid of ',' all you need to do is: convert the number value into a text value. To do this, first change the return type from Number to Text. And convert the value which is being returned from your formula to a text value, like this:

Text( 
  IF(
    (ISNULL(VALUE(End_Year__c))),
    VALUE(TEXT(YEAR(TODAY()))), 
    ( VALUE (End_Year__c) -  VALUE(Start_Year__c))
  ) 
)

This might not be an optimal solution, but it should do the trick you are looking for.

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.