Skip to main content
Participant
December 22, 2021
Question

Nested IF/THEN statements

  • December 22, 2021
  • 1 reply
  • 621 views

I'm trying to replicate the attached excel sheet as a PDF and I'm stuck on the meal calculations. Essentially, if someone departs after 12pm, they only get a half day of meal coverage. I have assigned the hour, minutes, and am/pm number values in order to get a value that the formula can compare.

 

In excel, it's the following:

IF (D9=J9, 0, IF (G9 > 0.5, 1, 0)) [this is in the Q column. my comparison value would be 12 the way I programmed it]

IF (D9=J9), 0 IF (M9 < 0.499998, 1, 0)) [this is in the Q column. my comparison value would be 11.99998 the way I programmed it]

 

D9=DepartureDate

J9=ReturnDate

G9=SumD [sum of the three dropdown departure times]

M9=SumR [sum of the three dropdown return times]

 

This is the script I put in custom calc:

var DepartureDate = this.getField("DepartureDate").valueAsString;
if (DepartureDate == "Return Date") event.value = 0;
else if (this.getField ("SumD") > 12) event.value = 1;

 

Both excel and PDF are attached. Any help appreciated.

This topic has been closed for replies.

1 reply

try67
Community Expert
Community Expert
December 22, 2021

Change:

else if (this.getField ("SumD") > 12)

To:

else if (Number(this.getField ("SumD").valueAsString) > 12)