I have a switch formula setup to calculate Experience Brief Due Date and Opportunity Case Due date based on the Estimate size of the project, when an item was added during Quarterly Planning.
Is there a way to add onto this formula to return the "Created" date if "When was this added to the Plan?" does not equal "Quarterly Planning"?
Current Formula:
IF({When was this added to the Plan?}="Quarterly Planning",SWITCH({Estimated size},"Small (2-4 weeks)",DATEADD({Target in market date},"-35","days"),
"Medium (4-6 weeks)",DATEADD({Target in market date},"-49","days"),"Large (6-8 weeks)",DATEADD({Target in market date},"-63","days")))
Best answer by kuovonne
Add the {Created} field as the third parameter of your IF() function.
IF(
{When was this added to the Plan?}="Quarterly Planning",
SWITCH( {Estimated size},
"Small (2-4 weeks)", DATEADD({Target in market date}, -35, "days"),
"Medium (4-6 weeks)", DATEADD({Target in market date}, -49, "days"),
"Large (6-8 weeks)", DATEADD({Target in market date}, -63, "days")
),
{Created}
)
Here is another version.
IF(
{When was this added to the Plan?} = "Quarterly Planning",
DATEADD(
{Target in market date},
SWITCH( {Estimated size},
"Small (2-4 weeks)", -35,
"Medium (4-6 weeks)", -49,
"Large (6-8 weeks)", -63
),
"days"
),
{Created}
)
And yet another version
IF(
AND(
{When was this added to the Plan?} = "Quarterly Planning",
{Target in market date},
{Estimated size}
),
DATEADD(
{Target in market date},
SWITCH( {Estimated size},
"Small (2-4 weeks)", -35,
"Medium (4-6 weeks)", -49,
"Large (6-8 weeks)", -63
),
"days"
),
CREATED_TIME()
)
