Real World Use of Mod Function
Bob Gossom <bobg@...
2014-10-22 15:27:22 GMT
I occasionally find myself trying to use the Mod function to deal with non-10 based numbering. Yesterday
for example I needed to get the fiscal year month, for a year that starts in July. (July = 1…June = 12).
The above example isn't hard to calc out without Mod, but I really like using clean, built-in calculations,
and enjoy extending my tool set.
My problem with using the mod function is that the last number in the sequence goes to 0. In the above example,
every month returns the expected value, except that June reports as "0", not 12. I've run into this every
time I've tried to use the function, and I've never found a workaround that wasn't at least as complicated
as a calculation not using Mod.
I understand the theory behind the function and why a mathematician wants that last number to be "0," but
I've never found it useful for solving a real world problem.
In the big scheme of things, this doesn't matter much, but it bugs me to have a function out there that I just
can't get to be useful. Is there something I'm not getting?
Absolute Advantage, Inc.
Mod ( month(date_field) + 6, 12)
returns 1 - 11 for July - May, 0 for June.
m = Mod ( month(date_field) + 6, 12),
case( m = 0,12, m)