I discovered today that Salesforce's MOD function (in formulae and workflows) will return a negative value if given a negative input.

MOD(-5, 12) = -5

This goes against the mathematical definition of Modulo, and makes it useless when dealing with dates.

The only way I can think of to handle this is:

MOD(MOD(Field__c, 12) + 12, 12)

But this doesn't seem like a sensible approach and will eat up compiled bytes rather quickly.

Is there a more sensible way to handle this?

  • You should open a case to report this bug. Other than that, you might be able to move the logic to Apex. – Adrian Larson Feb 5 '19 at 17:07
  • @AdrianLarson it's not a bug... – sfdcfox Feb 5 '19 at 17:08

Different languages handle negative modulo differently. If you check Wikipedia's Modulo operation article, you'll see that negative results might be returned if the divisor is negative, the dividend is negative, or never, or undefined. Salesforce's implementation is no more or less correct than any other implementation. Unfortunately, if you always need a positive result as in some other languages, you're basically limited to either the method you've chosen or choosing an arbitrarily large multiple of your modulus to add to:

MOD(120000000000+Field__c, 12)

For example, -1 would result in 119999999999, which ultimately results in the remainder 11.

I've even found at least one online calculator that operates in two modes, one of which being Salesforce's MOD operator

Finally, remember that Salesforce runs on Oracle, and so MOD works here just like it does in Oracle.

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.