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.
