This is how I currently have my formulas set up. However, I am receiving the error "Expecting boolean but found text" on the highlighted portion.
//Exclude from Report
If([Installation Stage]="Refused", "N/A",
[Installation Stage]="Remove from Scope", "N/A",
[Installation Stage]="Cancelled", "N/A",
[Installation Stage]="Pending", "N/A",
[Installation Stage]="3rd Party Pending", "N/A",
//Errors
If([Installation Stage]="Planned" or [Installation Stage]="Installed" and [Target Installation Date]=null, "Error - No Target Date",
//On Time v. Past Due
If([Installation Stage]="Planned" and Today()<=[Target Installation Date], "On Time",
If([Installation Stage]="Planned" and Today()>([Target Installation Date]+Days(3))=true,
"Past Due"),
If([Installation Stage]="Installed" and [Actual Installation Date]<=[Target Installation Date], "On Time",
If([Installation Stage]="Installed" and [Actual Installation Date]>([Target Installation Date]+Days(3)), "Past Due")
)
)
)
)
)
------------------------------
Jennifer Hall
------------------------------
Original Message:
Sent: 11-07-2022 13:37
From: Jennifer Hall
Subject: Building Nested Formulas
I am trying to assign a category ("On Time", "Past Due", "Exclude", and "Error") based on specific criteria (Installation Stage and dates). After which, I will create a pie chart showing the number of On Time Installations v. Past Due Installations.
Exclude
- Return "Exclude" if the Installation stage equals "Refused", "Cancelled", "Remove from Scope", "Pending", and "3rd Party Pending"
Errors
- Return "Error - No Target Date" if the Installation Stage equals "Installed" or "Planned" and the Target Date is blank
On Time v. Past Due
- If Installation Stage equals Planned
- Return "On Time" if Today() is less than or equal to than Target Date
- Return "Past Due" if Today() is greater than (Target Date + 3 days)
- If Installation Stage equals Installed
- Return "On Time" if Actual Install Date is less than or equal to Target Date
- Return "Past Due" if Actual Install date is greater than Target Date + 3 days)
------------------------------
Jennifer Hall
------------------------------