IF X is a Multiple of Y

[taran42]

I'm trying to produce an invoice every month for clients that need to be billed. Some clients are charged monthly, some quarterly and some annually. If a client is charged quarterly, then they only need to show up on every third month's invoice (depending on when they signed up for service). I was thinking I could use DateTimeDiff to determine how many months the client has been a customer, and then add them to the invoice or not.

So for quarters, if the client has been a customer for 3, 6, 9, 12, etc. months (essentially any multiple of 3), then they show up on this month's invoice. They would not show up on months 2, 4, 5, 7, 8, 10, etc. For annually billed clients, they would show up in multiples of 12 (12, 24, 36, etc.).

I was thinking about a formula something like below. Then I could filter out the True values for the monthly report.

IF [Fee Cadence] = "Monthly" THEN "True"
ELSEIF [Fee Cadence] = "Quarterly" THEN (
IF [this is a multiple of 3] THEN "True"
ELSE "False"
ENDIF)
ELSEIF [Fee Cadence] = "Annually" THEN (
IF [this is a multiple of 12] THEN "True"
ELSE "False"
ENDIF)
ELSE "False"
ENDIF

How can I determine if a number is a multiple of another? Is there a tool or function that would allow for this? Or perhaps a better method?

[Luke_C · Accepted solution]

Hi @taran42 

You could try the 'mod()' function. This will check to see if there is a remainder based on the number you enter.

The below example would cover your quarter scenario. It checks if the number in 'field1' is divisible by 3. If it returns 0, it is. Anything else would indicate it's not.

[taran42]

Ah ha! I get it now. I had actually tried the MOD() function already, but I didn't know how to interpret the results, so I didn't think it was working properly.

I was getting numbers all over the board, not realizing I was looking for only 0. Thanks @Luke_C for the help and clarification!