Skip to main content
Z
Zapien_Electric_Co___Inc_
Participant
February 3, 2016
Question

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

  • February 3, 2016
  • 2 replies
  • 1312 views

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!

This topic has been closed for replies.

2 replies

frameexpert
Community Expert
frameexpertCommunity Expert
Community Expert
February 3, 2016

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

www.frameexpert.com
G
George_Johnson
Inspiring
February 3, 2016

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.

Z
Zapien_Electric_Co___Inc_Author
Participant
February 3, 2016

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!

G
gkaiseril
Inspiring
February 3, 2016

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices