4 Replies Latest reply on Mar 4, 2015 7:53 AM by gkaiseril

    Converting excel formula into calculation script

    aishasuzan101

      How to convert this excel formula into calculation script in pdf?

      =IF(K10="PRF1120",13,IF(K10="PRF1020",10,IF(K10="PRO1020",8,IF(K10="PRF1140",5,IF(K10="ACC 1020",3,IF(K10="PRO1080",4,""))))))

        • 1. Re: Converting excel formula into calculation script
          George_Johnson MVP & Adobe Community Professional

          The custom calculation script should be something like:

           

          // Get the value of the input field

          var sVal = getField("K10").valueAsString;

           

          // Set the value of this field depnding on the value of the input field

          switch (sVal) {

          case "PRF1120":

              event.value = 13;

              break;

          case "PRF1020":

              event.value = 10;

              break;

          // repeat for other possibilities

           

          default:

              event.value = "";  // Blank this field

          }

           

           

          More information on the switch JavaScript statement is available in any decent JavaScript reference. Replace "K10" in the first line above with the actual name of the input field.

          • 3. Re: Converting excel formula into calculation script
            aishasuzan101 Level 1


            Hi.

            How about this formula?

             

            =DATE(YEAR(M9)+L9,MONTH(M9),DAY(M9))

            • 4. Re: Converting excel formula into calculation script
              gkaiseril MVP & Adobe Community Professional

              Using your Excel cell names as the field names:

               

               

              // Excel =DATE(YEAR(M9)+L9,MONTH(M9),DAY(M9))


              // document level funtions;

              function GetField(cName) {
              // get field object with error checking;
              var oField = this.getField(cName);
              if(cName == null) app.alert("Error accessing field " + cName + "\nPlease check the existence of the field and its name.", 1, 0);
              return oField;
              } // end GetField function;

              function Scand(cForamt, cDateString) {
              // convert date string to a date object with error checking;
              var oDate = util.scand(cFormat, cDateString);
              if(oDate == null) app.alert("Error converting " + cDateStirng + " with format of " + cFormat +"\Please check the format of the date field", 0, 1);
              return oDate;
              } // end Scand function;

              // end document level functions;

               

              // custom JavaScript calculation for the target field;
              // format for dates;
              var cFormat = "d-mmm-yyyy";
              // field name for formatted input date;
              var cDateField = "M9";
              // field name for the number of years to add;
              var cAddYears = "L9";
              // get the field objects;
              var oDateField = GetField(cDateField);
              var oAddYears = GetField(cAddYears);
              event.value = ""; // clear the target field;
              // convert date string to date object;
              var oDate = Scand(cFormat, oDateField.valueAsString);
              // get the full year from the date object;
              var nFullYear = oDate.getFullYear();
              // add number of years to the full year;
              nFullYear += oAddYears.value;
              // rest the date object for the new year value;
              oDate.setFullYear(nFullYear);
              // set the target field with the new date;
              event.value = util.printd(cFormat, oDate);

              // end custom JavaScript calculation;

               

              More information about date and time calculations are included in the Acrobat JavaScript documentation available for free from Adobe and JavaScript documentation available for free from Mozilla Development.