+ Start a Discussion
J BengelJ Bengel 

Returning an integer value in a formula field

For all of their similarity to Excel formulas, there appears to be no equivalent to the INT() function in Salesforce. I have a long list of "Things I Can't Believe Salesfore Doesn't Do and Have Been On the Idea List for 14 Years", but I can usually work around the things on that list. In this case, though, the output from this formula stubbornly refuses to return a value with no decimal places (even though it is defined as such).
Data Type Formula
Return type Number
Decimal Places 0  
FLOOR((TODAY() - hed__Contact__r.Birthdate) / 365.2425)
Given an input of 8/1/1980 and a current date of 7/9/2021, the formula returns 40.00. Which is correct, since the result of the core calculation is 40.93718557, and I'm only concernign myself with the interger portion of the number. But two things jump out at me here. First the documentation of the FLOOR() function is at best misleading, because it's described as "rounding down to the nearest integer", which is only half right. It is technically rounding down, but it's not returning an integer -- or at least it's not returning something that's displayed as integer.  I suspect that this is related to the fact that Salesforce doesn't distinguish between various numeric data types when defining a custom field. Everything is lumped into "Number" which appears to be defined as Decimal, because defining it with 0 decimal places does not make it an integer.
Which is fine, until you need an integer value. In the example, if you see 40.00 on the screen, you don't get the idea hthat this contact is mere days away from being 41 -- you're displaying his age as exactly 40 years, to two digits of precision.
Creating the field with a return type of Text and wedging the number in there to strip off the trailing decimals is a workaround that fixes that problem (at least I assume you can do that -- but I haven't actually tried it), and it's a fine solution until you need to use the numeric value in a different calculation where some warning is displayed if the contact is under 18 years of age. At that opint you're not only convertign the initial calculation result to text, but you have to convert it back to a number on the other end.

For all its sophistication, this platform has some pretty glaring omissions of what should be very common, basic functionality.
J BengelJ Bengel
And one hour later... give or take, it fixes itself. So now I can take it off the list of "Things I Can't Believe Salesfore Doesn't Do and Have Been On the Idea List for 14 Years" and add it to the list of "Salesforce Things That Fix Themselves If You Wait A While".

I even tried deleting the field definition, and adding it back from scratch, and there was no joy in Mudville. Until there was.