5 Replies Latest reply on Mar 3, 2014 12:43 PM by try67

    Translating Excel Formula to Java Script

    Cierdwyn

      Please assist in translating the following formula to JS.  I kept the field names the same as the cell address.  =IF(D18<(F11+30),"Yes", "No") 

       

      Fields are dates, if that matters.

        • 1. Re: Translating Excel Formula to Java Script
          try67 MVP & Adobe Community Professional

          Working with dates in PDF files is not as easy as in Excel... First of all, you have to convert the strings into Date objects. For that you need to know what format they're in. Also, what does the "30" stand for here? 30 seconds? days? months? years?

          • 2. Re: Translating Excel Formula to Java Script
            Cierdwyn Level 1

            30 days.  date is mm/dd/yy format.  Can you please assist?  Thank you.

            • 3. Re: Translating Excel Formula to Java Script
              try67 MVP & Adobe Community Professional

              Use this code as the custom calculation code of the field:

               

              var v1 = this.getField("F11").valueAsString;

              var v2 = this.getField("D18").valueAsString;

              if (v1 && v2) {

                  var d1 = util.scand(this.getField("mm/dd/yy", v1));

                  var d2 = util.scand(this.getField("mm/dd/yy", v2));

                  d1.setDate(d1.getDate()+30);

                  event.value = (d2.getTime()<d1.getTime()) ? "Yes" : "No";

              } else event.value = "";

              • 4. Re: Translating Excel Formula to Java Script
                Cierdwyn Level 1

                Thank you for the assistance, but it is not working ans I have tried several times.

                 

                 

                I am working in Acrobat 9 if that makes a difference.  basically i want to compare two dates and if less than or equal to f11+30(30 days from funding date) to give back a yes or no answer.

                Javascript debugger has the following to say: 

                this.getField("TimePrint") is null

                4:Field:Calculate

                TypeError: this.getField("TimePrint") is null

                4:Field:Calculate

                this.getField("F11") is null

                1:AcroForm:ee18:Calculate

                TypeError: this.getField("F11") is null

                1:AcroForm:ee18:Calculate

                this.getField("TimePrint") is null

                4:AcroForm:F2:Calculate

                TypeError: this.getField("TimePrint") is null

                4:AcroForm:F2:Calculate

                this.getField("F11") is null

                1:AcroForm:ee18:Calculate

                TypeError: this.getField("F11") is null

                1:AcroForm:ee18:Calculate

                SyntaxError: missing ; before statement

                1:

                this.getField("TimePrint") is null

                4:AcroForm:F2:Calculate

                TypeError: this.getField("TimePrint") is null

                4:AcroForm:F2:Calculate

                this.getField("F11") is null

                1:AcroForm:ee18:Calculate

                TypeError: this.getField("F11") is null

                1:AcroForm:ee18:Calculate

                 

                 

                Any assistance is appreciated.

                 

                 

                I also have two other formulas that are again comparisons of dates.  I have the excel formulas, and I retained the same field names in the PDF.                                                                                                                                                                                

                 

                H59 formula:  =AND(F4>F11+1),F5>F11+1))    D60 formula:  =IF(H59,"OK","Need Extended")

                 

                                                                                                                                                                                                                                                                                                    H60 formula:  =AND(F58>=F11,F59>=(F11-14))   f60 formual:   =IF(H60,"OK","Need Revised")

                • 5. Re: Translating Excel Formula to Java Script
                  try67 MVP & Adobe Community Professional

                  These errors basically mean that the field names are not correct. There are no fields with those names in your file. Keep in mind that JS is case-sensitive and that you have to include any spaces or special characters in the field's name.