Formula field to calculate difference between years

Question

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.

Answer 1

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.

Answer 2

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.