Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Dec 12, 2023 08:57 PM
Hi All
I am trying to build a formula to reflect Master project status based on (sub)project status. Here's what I have. So the hierarchy should be:
- If ANY of the project status contains "ongoing", then master project status to reflect "ongoing"
- If "ongoing" does not exist and any of the project status is "planned", then master project status to reflect "planned"
- If "ongoing" and "planned" do not exist and ALL of the project status is "hold", then master project status to reflect "hold"
- The remaining should reflect as "concluded"
So in the below table, the correct reflection should be:
- Row 1, 3, 4, 6, 8 should be "ongoing" (because "ongoing" applies to any)
- Row 5, 7 should be "planned"
- Row 2, 9, 10 should be "concluded"
Thank you!!
1 Reply 1
Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 06, 2024 08:02 AM
Hey Sharon,
Might try this:
IF(
FIND("ongoing", {Project Status}) > 0,
"ongoing",
IF(
FIND("planned", {Project Status}) > 0,
"planned",
IF(
AND(
FIND("hold", {Project Status}) > 0,
NOT(FIND("ongoing", {Project Status})),
NOT(FIND("planned", {Project Status}))
),
"hold",
"concluded"
)
)
)
