Skip to main content
C
Chris_N24
Known Participant
December 14, 2020
Answered

Translating excel formula to javascript

  • December 14, 2020
  • 2 replies
  • 1714 views

Hello all,

  so I'm running into a bit of a problem. I'm trying to see if there's a way to translate the formula (in the formula bar) in the attached pic into a javascript. I have the same 4 field on a pdf file in the exact same order, but the names are different (01, 02, 03, 04) so the "formula" for the "04" field I'm looking for should go along the lines of:

=01-03+02

If you are familiar with excel formulas you know at 04 would pop up in DATE format.

 

Any and all help would be GREATLY APPRECIATED cause my brain is fried after trying to look for a similar javascript for the past 5 hours. Apologies if I seem to be rambling, I'm just out of it.

This topic has been closed for replies.
Correct answer Nesa Nurani

Assuming your fields are named
"Date", "Days Requested", "Days Substracted" and "Expiration Date"
Put code in "Expiration Date".
(In "Prepare form" mode, right click on "Expiration Date" field and select properties,
then select "calculate" tab and put code in "Custom calculation script".
If you want to rename your fields thats ok, just rename them in code also.
You can also change Date format if you wish, change mm/dd/yyyy to format you required.
"Expiration Date" field should auto update.

 

var req = this.getField("Days Requested").value;
var sub = this.getField("Days Substracted").value;
var cal = sub+req;
var tdate = this.getField("Date").valueAsString;
if(tdate == "") event.value = "";
else{
var cDate = util.scand("mm/dd/yyyy", tdate);
cDate.setDate(cDate.getDate()-cal);
event.value = util.printd("mm/dd/yyyy", cDate);}

2 replies

Nesa Nurani
Nesa NuraniCorrect answer
Community Expert
December 14, 2020

Assuming your fields are named
"Date", "Days Requested", "Days Substracted" and "Expiration Date"
Put code in "Expiration Date".
(In "Prepare form" mode, right click on "Expiration Date" field and select properties,
then select "calculate" tab and put code in "Custom calculation script".
If you want to rename your fields thats ok, just rename them in code also.
You can also change Date format if you wish, change mm/dd/yyyy to format you required.
"Expiration Date" field should auto update.

 

var req = this.getField("Days Requested").value;
var sub = this.getField("Days Substracted").value;
var cal = sub+req;
var tdate = this.getField("Date").valueAsString;
if(tdate == "") event.value = "";
else{
var cDate = util.scand("mm/dd/yyyy", tdate);
cDate.setDate(cDate.getDate()-cal);
event.value = util.printd("mm/dd/yyyy", cDate);}
C
Chris_N24Author
Known Participant
December 14, 2020

Still though, I REALLY appreciate your help!

A
Asim123
Inspiring
December 14, 2020

Oh I just changed the sub+req to sub-req and it started working perfectly. Again, thank you so much! You really saved me a lot of headache.


If you changed to sub-req then you get same result as you get with Nesa's first code, but you said that is not correct?

example:

Nesa's 1st code "100-(20+10)=70".

Nesa's 2nd code (where you asked for "Date - Days Subtracted + Days Requested") "100-20+10=90".

your code "100-20-10=70".
Or am I missing something here?

C
Chris_N24Author
Known Participant
December 14, 2020

Also, does the javascript automatically calculate the new date? Or do I have to click on something to make the "New Expiration Date" appear? I have no idea where I'm supposed to insert this javascript so help in navigating where this script is supposed to be posted would be greatly appreciate too. I'm sorry for being so demanding, I'm still new to this pdf stuff.

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