Hi.
So I've solved this problem but it's not elegant. Anyone got a better way to find an age (in this case on the date 1st April 2013) and state it in the format "00y 00m" (years and months) for easy reading?
Here's my solution:
TRUNC((DATEVALUE("1/9/13") - 'DOB') / 365.24) & "y " & TRUNC(12 * (((DATEVALUE("1/9/13") - 'DOB') / 365.24) - TRUNC(((DATEVALUE("1/9/13") - 'DOB') / 365.24)))) & "m"
...where 'DOB' is a field name containing a datevalue (the person's date of birth).
I couldn't find a 'MODULUS' function, or a "\" (remainder rather than divide "/") operator or a neat way to do a sub-calculation... perhaps we need one?
Thanks.
This doesn't seem like it has anything to do with FormsCentral. What application are you doing this coding in?
This is indeed in FormsCentral in the replies grid where, if you type an 'equals' character into a box you get to put a formula in all the fields in that column. It's a lot like Excel but I can't find thd modulus operator.
The functions are listed in the help guide under 'shortcuts and functions'.
I get it now. I don't see a modulo operator either, but you could do the calculation if there were an function that gave you the integer part of a number. I looked at the documentation and noticed this:
ROUNDOWN | ROUNDUP(number1, num_digits) | Rounds a number up, away from 0 |
The ROUNDOWN should give this to you, though the documentation seems to have an error in it (as it shows ROUNDUP) and the correct function is ROUNDDOWN.
For the remainder of X/Y, the following should work: X - Y * ROUNDDOWN(X/Y, 0)
It seems like the documentation for the ROUNDUP function is missing.