4 Replies Latest reply on Mar 9, 2015 12:35 AM by aishasuzan101

    convert

    aishasuzan101

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

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

        • 1. Re: convert
          George_Johnson MVP & Adobe Community Professional

          We'll need more information. If the value in M9 is supposed to be a date, what format would it be? For example, yyyy/mm/dd

           

          Is the idea that the formula is taking one date and adding a certain number of years to it?

          • 2. Re: convert
            aishasuzan101 Level 1

            yes. a date and adding number of years.

            • 3. Re: convert
              gkaiseril MVP & Adobe Community Professional

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


              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;

               

              // 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 = Number(oDate.getFullYear());
              // add number of years to the full year;
              nFullYear += Number(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);

               

              The script could be shortened by not declaring variables for each step. Or creating a function to read the values compute and return the new date string.

              • 4. Re: convert
                aishasuzan101 Level 1


                Hi.

                 

                this is what I entered in the custom calculation script:

                 

                // DestroyDate1 =DATE(YEAR(ProjClosureDate1)+RetentionPeriod1,MONTH(ProjClosureDate1),DAY(ProjClosureDate 1))


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

                // custom JavaScript calculation for the target field;
                // format for dates;
                var cFormat = "d-mmm-yy";
                // field name for formatted input date;
                var cDateField = "ProjClosureDate1";
                // field name for the number of years to add;
                var cAddYears = "RetentionPeriod1";
                // 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);

                 

                 

                THE ONLY PROBLEM I AM FACING NOW IS THAT THE "DestroyDate1" field is showing the current date. It should be empty prior to the calculation.

                please help.

                 

                thanks