6 Replies Latest reply on Feb 5, 2016 9:35 AM by gkaiseril

    How to convert an Excel Formula to a Custom Calculation Script in a Adobe Acrobat 9 Form?

    Zapien Electric Co., Inc.

      Hello,

       

      I am not familiar with Javascript and need some help in converting the following Excel Formula so that I can enter it into a Custom Calculation Script in a Adobe PDF Form. Here is the formula:

       

      =IF(OR(F9="",F11="",E11=""),"",TEXT(F11,"00\:00")-TEXT(E11,"00\:00")-F9)

       

      Where "F9", "F11" and "E11" are named the same on my Adobe PDF Form.

       

      Thank you for any help you can provide!

        • 1. Re: How to convert an Excel Formula to a Custom Calculation Script in a Adobe Acrobat 9 Form?
          George_Johnson MVP & Adobe Community Professional

          Is that formula used to do time arithmetic? If so, you'll have to provide more details. Performing date/time arithmetic in JavaScript isn't as simple as it is in Excel.

          • 2. Re: How to convert an Excel Formula to a Custom Calculation Script in a Adobe Acrobat 9 Form?
            frameexpert Level 4

            I am not familiar with Excel formulas but if you can spell out in pseudo-code what is supposed to happen, I can translate it to JavaScript. Thanks. -Rick

            • 3. Re: How to convert an Excel Formula to a Custom Calculation Script in a Adobe Acrobat 9 Form?
              Zapien Electric Co., Inc. Level 1

               

              Yes, this formula is used to do time arithmetic. I'm not sure if this image will help. In this formula all I'm doing is calculating the time difference from the time in and time out minus the lunch time to get a total hours worked per day. I'm using 24hour format on the time in and time out cell. On the lunch time I'm using the "HH:MM" format. I'm also using "HH:MM" format on the total time worked. Also the total time does not display error or a value until all 3 cells (E11, F9, F11) have been filled out.

               

              Thank you for any help you can provide!

              • 4. Re: How to convert an Excel Formula to a Custom Calculation Script in a Adobe Acrobat 9 Form?
                gkaiseril MVP & Adobe Community Professional

                Excel performs a translation of the text time value to the number of milliseconds since  the Epoch date and then computes the difference between the 2 times in milliseconds with the time zone offsets removed which eliminates the issue of the times occurring during a change from standard time to daylight savings time. That value is then converted back to the elapsed time value. Note that elapsed time values are not the same as the time of day values. Time of day values range form "0000" to "2359" while elapsed times and the sum of elapsed times can exceed 24 hours.

                 

                I also discourage using military time since leading zeros in JavaScript are usually removed from the numeric value.

                 

                So it appears you want the formula for computing the elapsed time for Monday, but this formula could also be applied to any of the days of the week. By using the absolute fields for a day means that the script will need to repeated and adjusted for each day of the week. It might be beneficial to create a function or series of functions to perform this task using supplied parameters to the function and allow the function to return the computed value. Using this method means there is only location the calculation is preformed and if one needs to correct a coding error, then there is only one script to be changed.

                 

                It appears the Excel might not adjust for times the overlap the changes for daylight savings and standard times. Will you shifts include this interval. If so, then the date becomes and important piece of information needed for the calculation.

                 

                Do you need to compute the total elapsed or worked time for a week or 2 weeks?

                 

                If so this calculation becomes much easier if the daily elapsed time value is in minutes and a custom format is applied to the daily field. With the approach, one only needs to sum the daily values and apply the custom format. Using a document level script for the formatting makes applying the custom format a simple call to the formatting function.

                • 5. Re: How to convert an Excel Formula to a Custom Calculation Script in a Adobe Acrobat 9 Form?
                  Zapien Electric Co., Inc. Level 1

                  The change to non military time would not be an issue.

                   

                  1) My shifts will not include the daylight savings and standard time interval.

                   

                  2) I will not need the total hours worked for a week or 2 calculated.

                   

                  Will only be calculating the daily hours per day for one week on this form.

                   

                  Thank you for any help you can provide!

                  • 6. Re: How to convert an Excel Formula to a Custom Calculation Script in a Adobe Acrobat 9 Form?
                    gkaiseril MVP & Adobe Community Professional

                    I tried your formula in Excel and it works for the daily calculation but the results do not sum correctly for a any total time greater than 24 hours.

                     

                    Fortunately Acrobat JavaScript does not have this limitation if implemented properly.

                     

                    I have used functions extensively in the final form because so many of the actions needed to be completed are repeated so many times with a single field and then within summation. I am also taking advantage of the fact that PDF forms can display a field's value in a format other than the field's value. Time values are character strings and not numbers. To do any computation one needs to convert the character string into a single numeric value. So if the total hours worked for one day were say 7:30 hours then to get the sum I would need to convert each day's total string into a numeric value like 450 minutes and then total each days worked value in minutes and convert the result back to the character string like 37:30 if the total hours worked for each day were 7:30 for 5 days.

                     

                    One also needs to write a custom JavaScript calculation and that requires getting the field object for each named field, so I have a function that will return the field object for a named field and if the field cannot be found issues an error message.https://cloud.acrobat.com/file/a8b12b41-8c83-48f4-84e6-5c018115a13e

                    https://cloud.acrobat.com/file/a8b12b41-8c83-48f4-84e6-5c018115a13e

                    Simple Timesheet just using hours and minutes for the calculations. It does not work for time intervals over the standard/day light savings time changed or time zone changes.