Skip to main content
Question

Building Nested Formulas

  • November 7, 2022
  • 0 replies
  • 104 views
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
------------------------------
This topic has been closed for replies.

  • JenniferHall
  • Author
  • Registered
  • November 7, 2022
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
------------------------------
Terms & ConditionsAccessibility statement