Calculating Fields

[Guest]

Hello, I am trying to calculate mutiple fields. The formula in Excel would look something like this  =(D8*G8)*EXP(-0.693/C8*(F8-E8)). Any help would be appreciated. Thanks

C	D	E (YYYY-MM-DD)	F (YYYY-MM-DD)	G	H (Result)
157861.05	50130	1998-04-01	2016-07-06	2.9350	142885

[gkaiseril]

What is the computational value of "1998-04-01"?

It cannot be the simple arithmetic result. Is it the number of days, hours, seconds, or milliseconds from some Epoch date?

Acrobat's date calculations are based on the number of milliseconds since Midnight January 1, 1970. If Excel uses a different unit or epoch date, then some correction will be needed to be made.

[Guest]

Hi there, the computational value is the number of days.

For example if I wanted the computational value of these two dates ( 2015-01-01 and 2016-01-01) the result would be 365.

Hope this helps.

Claude

[gkaiseril]

Assuming you have given the PDF form fields the same names as the cells in Excel, the following custom calculation script should work.

/*
Excel formula to convrt:Nun
=(D8*G8)*EXP(-0.693/C8*(F8-E8))
*/

function GetField(oDoc, cName, bWarning)
{
// return field object with error checking;
if(typeof bWarning == "undefined")
{
  bWarning = true;
}
var oField = oDoc.getField(cName);
if(oField == null && bWarning == true)
{
  app.alert("error accessing field " + cName, 1, 0);
}
return oField;
} // end GetField function;

function Scand(cFormat, cValue, bWarning)
{
// convert date string with foramt to date object;
if(typeof bWarning == "undefined")
{
  bWarning = true;
}
var oDate = util.scand(cFormat, cValue);
if(oDate == null && bWarning == true)
{
  app.alert("Error convertng date " + cValue + " with format " + cFormat, 1, 0);
}
return oDate;
} // end Scand function;

// clear result;
event.value = "";
// get various fields to process;
var oC8 = GetField(this, "C8");
var oD8 = GetField(this, "D8");
var oE8 = GetField(this, "E8"); // start date string;
var oF8 = GetField(this, "F8"); // end date string;
var oG8 = GetField(this, "G8");
// process if we have all the data and c8  value (divisor) is not zero;
if(oC8.valueAsString !="" && oC8.value != 0 &&  oD8.valueAsString != "" && oE8.valueAsString != "" && oF8.valueAsString != "" && oG8.valueAsString != "")
{
// convert E8 date string to number of days since epoch date;
var oE8Date = Scand("yyyy-mm-dd", oE8.value);
var nE8 = Math.floor(oE8Date.getTime() / (1000*60*60*24));
// convert F8 date string to number of days snce epoch date;
var oF8Date = Scand("yyyy-mm-dd", oF8.value);
var nF8 = Math.floor(oF8Date.getTime() / (1000 * 60* 60 * 24));
// nF8 - nE8 is the difference in days;
// compute field values and round the result;
// Excel formula: =(D8*G8)*EXP(-0.693/C8*(F8-E8))
event.value = (oD8.value * oG8.value) * Math.exp(-0.693 / oC8.value *(nF8 - nE8));
}

The script includes some functions that include validation of the existence of the fields and the validity of entered dates.

If you are going to continue to convert Excel files with complex formulas you might want to study Acrobat's JavaScript Library and the MDN JavaScript Reference.

This forum is for helping others understand how Acrobat forms and Acrobat JavaScript work not do their work. But your issue and lack of Acrobat Form and JavaScript knowledge would have required a lengthy discussion of so many issues, it was just easier to provide you with a solution. you can study the links to and the code to see how the Excel formula is converted into JavaScript and how the calculation is done using various objects.

If I were repeating this calculation repeatedly for rows of input values, I would convert the script into a function that takes the various input values as parameters  and returns computed values. I would also use hierarchical field names so I could change a single value to compute the new row of fields.

[Guest]

Thank you, but it did not work. I am able to get three of the calculations to work individually on my own, but not when I try to combine them. That's Ok I will stick to an excel spreadsheet for this specific type of Log sheet and use adobe for basic forms because nothing bothers me more than presumptuous people.

Thanks again.

[gkaiseril]

What does not work?

Have you looked at the JavaScript console?

I have given you for free a script  that does work for 3 fields but you cannot get it to work for a 4th field. Have you checked that the 4thi field has the correct data?

I assume you are getting paid for using the provided free script.