3 Replies Latest reply on Jan 13, 2013 4:13 PM by George_Johnson

    Finding a remainder after division

    Pebblesculpt Level 1

      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.

        • 1. Re: Finding a remainder after division
          George_Johnson MVP & Adobe Community Professional

          This doesn't seem like it has anything to do with FormsCentral. What application are you doing this coding in?

          • 2. Re: Finding a remainder after division
            Pebblesculpt Level 1

            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'.

            • 3. Re: Finding a remainder after division
              George_Johnson MVP & Adobe Community Professional

              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.