Convert excel formula to custom calculation script need help

Explorer ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

I have working excel formula : 

IF(C6<J11;0;IF(C6>L11;0;IF(AND(C6<J11;C7<J11);0;IF(AND(L11>=C6>=J11;C7>L11);L11-C6;C7-C6))))

I am trying to convert this formula to Adobe custom calculation JavaScript. I was writen this code:

var C6 = this.getField("Zacetek").valueAsString;
var C7 = this.getField("Konec").valueAsString;

var J11 = this.getField("zNs1").valueAsString;
var L11 = this.getField("kNs1").valueAsString;
var J13 = this.getField("zNs2").valueAsString;
var L13 = this.getField("kNs2").valueAsString;

var J19 = this.getField("zSs1").valueAsString;
var L19 = this.getField("kSs1").valueAsString;
var J21 = this.getField("zSs2").valueAsString;
var L21 = this.getField("kSs2").valueAsString;

var J27 = this.getField("zVs").valueAsString;
var L27 = this.getField("kVs").valueAsString;

function a()
{
var sStart = C6;
var sEnd = C7;
var dStart, dEnd, diff;
if ( ( sStart != "") && ( sEnd != ""))
{
dStart = util.scand("dd.mm.yyyy", sStart);
dEnd = util.scand("dd.mm.yyyy", sEnd);
diff = (dEnd - dStart) / 864e5;
event.value = diff;
}
else
{
event.value = 0;
}
}

function b()
{
var sStart = C6;
var sEnd = L11;
var dStart, dEnd, diff;
if ( ( sStart != "") && ( sEnd != ""))
{
dStart = util.scand("dd.mm.yyyy", sStart);
dEnd = util.scand("dd.mm.yyyy", sEnd);
diff = (dEnd - dStart) / 864e5;
event.value = diff;
}
else
{
event.value = 0;
}
}

function c()
{
var sStart = L21;
var sEnd = C7;
var dStart, dEnd, diff;
if ( ( sStart != "") && ( sEnd != ""))
{
dStart = util.scand("dd.mm.yyyy", sStart);
dEnd = util.scand("dd.mm.yyyy", sEnd);
diff = (dEnd - dStart) / 864e5;
event.value = diff;
}
else
{
event.value = 0;
}
}

function d()
{
 if(C6 < J11) 
   {event.valueAsString = 0;}
 else
   {if(C6 > L11)
      {event.valueAsString = 0;}
    else
      {if((C6<J11) && (C7<J11))
        {event.valueAsString = 0;}
        else
          {if((L11>=C6>=J11) && (C7>L11))
        {event.valueAsString = b();}
            else
        {event.valueAsString = a();}
           }
       }
    }
}
//
//
event.valueAsString = d();

But the custom calculation script I wrote don't calculate right.

What I am doing wrong? Can someone help me? 

Any help is welcome. 

TOPICS
Edit and convert PDFs , How to , JavaScript , PDF forms

Views

112

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Explorer , Sep 20, 2022 Sep 20, 2022

After several days of searching for errors in the script, I finally succeeded. I have a fully working script. Thanks to everyone who pointed me in the right direction and helped me find a solution.

Likes

Translate

Translate
Enthusiast ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

You have 4 different scripts in there. Can you just explain in words what you try to achieve?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

I am trying to calculate how many days are between two dates. These two dates are dependent on the other two dates.

I'm counting down the days of renting a motor home. The price depends on the number of days and the season in which the camper is rented. I am trying to calculate how many days there are in the selected season.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

This calculation work in excel with formula =IF(C6<J11;0;IF(C6>L11;0;IF(AND(C6<J11;C7<J11);0;IF(AND(L11>=C6>=J11;C7>L11);L11-C6;C7-C6))))

C6 is rental start date

C7 is rental end date

J11 is season start date

L11 is season end date

What I am trying is to convert excel formula to custom Adobe javascript. Function c() in my script is for second calculation which I will add when this first part will work as it should.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

Are you using that script in one field?

Change:

event.valueAsString = 0

to:

event.value = 0;

 

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

Yes I am using this script in one field.

I was change as you propose, without success

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

I was just referring to a bug, not a solution for your problem.

If you use it like this:

{if((C6<J11) && (C7<J11))

you are comparing two strings which is wrong, if you wish to compare two dates search this forum on how to compare two dates, there are plenty of script for that.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

I wrote new script for function d(). It is not solution but can you tell me if I am looking for solution in right direction.

function d()
{
var sStart = C6;
var sEnd = C7;
var snStart = J11;
var snEnd = L11;
var dStart, dEnd, dnStart, dnEnd;
if ((sStart != "") && (sEnd !=""))
{
dStart = util.scand("dd.mm.yyyy", sStart);
dEnd = util.scand("dd.mm.yyyy", sEnd);
dnStart = util.scand("dd.mm.yyyy", snStart);
dnEnd = util.scand("dd.mm.yyyy", snEnd);
{if(dStart.getTime() < dnStart.getTime()) 
   {event.value = 0;}
else
{if(dStart.getTime() > dnEnd.getTime())
   {event.value = 0;}
else
{if((dStart.getTime() < dnStart.getTime()) && (dEnd.getTime() < dnStart.getTime()))
   {event.value = 0;}
else
{if((dnEnd.getTime() >= dStart.getTime() >= dnStart.getTime()) && (dEnd.getTime() > dnEnd.getTime()))
   {event.valueAsString = b();}
else
    event.valueAsString = a(); 
}
}
}
}
}
}

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 06, 2022 Sep 06, 2022

Copy link to clipboard

Copied

I was try with this custom calculation script for function d ()

function d()
{(C6<J11?0:(C6>L11?0:((C6<J11&&C7<J11)?0:((L11>=C6>=J11&&C7>L11)?b():a()))))}
event.valueAsString = d();

But the calculation is still not working correct

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Sep 20, 2022 Sep 20, 2022

Copy link to clipboard

Copied

LATEST

After several days of searching for errors in the script, I finally succeeded. I have a fully working script. Thanks to everyone who pointed me in the right direction and helped me find a solution.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines