IIf and Switch Functions in Microsoft Project

Microsoft Project provides powerful functions like IIf and Switch to create conditional expressions for custom fields. These functions allow users to perform logical operations and derive dynamic results based on specified criteria. This article explores how to use these functions with practical examples.

The IIf Function

The IIf function is used to evaluate a condition and return one value if the condition is true and another value if the condition is false.

Syntax:

IIf(condition, value_if_true, value_if_false)

Parameters:

  • condition: The logical condition to evaluate.
  • value_if_true: The value returned if the condition is true.
  • value_if_false: The value returned if the condition is false.

Example 1: Flagging Long Tasks

Flag tasks with durations longer than 10 days:

IIf([Duration] > 10, "Yes", "No")

Steps:

  1. Go to "Project" > "Custom Fields."
  2. Create a new Text field called "Long Task."
  3. Enter the formula: IIf([Duration] > 10, "Yes", "No").
  4. Apply the field to tasks.

Example Usage:

Task Name       | Duration | Long Task
--------------- | -------- | ----------
Foundation      | 12 days  | Yes
Roofing         | 8 days   | No

Example 2: Highlighting Critical Tasks

Flag critical tasks as "Critical" and non-critical tasks as "Non-Critical":

IIf([Critical], "Critical", "Non-Critical")

The Switch Function

The Switch function is used to evaluate multiple conditions and return a value corresponding to the first true condition.

Syntax:

Switch(condition1, value1, condition2, value2, ..., conditionN, valueN)

Parameters:

  • condition1, condition2, ...: Logical conditions to evaluate.
  • value1, value2, ...: Values returned if the respective condition is true.

Example 1: Categorizing Task Priority

Categorize tasks based on their priority:

Switch([Priority] >= 900, "High", [Priority] >= 500, "Medium", [Priority] < 500, "Low")

Steps:

  1. Go to "Project" > "Custom Fields."
  2. Create a new Text field called "Task Priority."
  3. Enter the formula: Switch([Priority] >= 900, "High", [Priority] >= 500, "Medium", [Priority] < 500, "Low").
  4. Apply the field to tasks.

Example Usage:

Task Name       | Priority | Task Priority
--------------- | -------- | -------------
Foundation      | 950      | High
Roofing         | 700      | Medium
Finishing       | 400      | Low

Example 2: Task Status Based on Percent Complete

Determine task status based on percentage complete:

Switch([% Complete] = 100, "Completed", [% Complete] > 0, "In Progress", [% Complete] = 0, "Not Started")

Comparison Between IIf and Switch

Both functions are useful for conditional logic but serve different purposes:

  • IIf: Suitable for simple conditions with two possible outcomes.
  • Switch: Ideal for handling multiple conditions and outcomes.

Practical Example

Consider a construction project:

Task Name       | Duration | % Complete | Task Status
--------------- | -------- | -----------| -----------
Foundation      | 10 days  | 100%       | Completed
Superstructure  | 20 days  | 50%        | In Progress
Roofing         | 5 days   | 0%         | Not Started

The Task Status field is calculated using the Switch function to determine the progress of each task.

Conclusion

The IIf and Switch functions in Microsoft Project provide flexibility for creating dynamic calculations and custom fields. By using these functions, you can enhance your project tracking and reporting capabilities.




Advertisement