4 Replies Latest reply on Sep 29, 2014 7:37 PM by JulesNotSoPro

# 'Truncate' Excel Formula in JavaScript

I am trying to translate an Excel formula into Java script and I am way out of my depth…. Could anyone assist?

This is the formula in Excel:  =TRUNC((B2*(A2/100))/365,2)

In my Adobe Pro Document B2 is a field called 'RAD' and A2 is a field called "MPIR'.

I want to translate the truncate formula to JavaScript so that my “DAP_ONLY” field calculation in Adobe Pro does not round up (as it seems to be the default setting)

At the moment, I have the following simplified field notation set to (RAD*(MPIR/100))/365 for my 'DAP_ONLY' field and the format set to number, 2 decimal points. Which works if I want the result to round up but I actually need the result to be truncated instead.

I have read material on the math.trunc function in JavaScript but don’t know how to apply it as I don't speak JavaScript ....  Is that something you can help with?

Thanks!

• ###### 1. Re: 'Truncate' Excel Formula in JavaScript

Use the Math.floor() method to round a number down to the nearest integer.

Have a look here:

http://www.w3schools.com/jsref/jsref_floor.asp

• ###### 2. Re: 'Truncate' Excel Formula in JavaScript

thanks but where do I use it? in my actual field calculation script

If I write Math.floor((RAD*(MPIR/100))/365) either in the simplified field notation or the custom calculation script it does not seem to work....

• ###### 3. Re: 'Truncate' Excel Formula in JavaScript

The Math.floor method is not equivalent to the TRUNC function. It is for positive values, but for negative values you should use Math.ceil. Here's what the script might look like:

// Custom calculation script

(function () {

// Get the field values as numbers

var MPIR = +getField("MPIR").value;

// Perform the calculation

var result = RAD * MPIR / 100 / 365;

// Truncate the result to two decimal places

if (result >= 0) {

event.value = Math.floor(result * 100) / 100;

} else {

event.value = Math.ceil(result * 100) / 100;

}

})();

• ###### 4. Re: 'Truncate' Excel Formula in JavaScript

thank you!!