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.
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.
Copy link to clipboard
Copied
You have 4 different scripts in there. Can you just explain in words what you try to achieve?
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.
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.
Copy link to clipboard
Copied
Are you using that script in one field?
Change:
event.valueAsString = 0
to:
event.value = 0;
Copy link to clipboard
Copied
Yes I am using this script in one field.
I was change as you propose, without success
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.
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();
}
}
}
}
}
}
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
Copy link to clipboard
Copied
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.