Help

Adding additional formulas to an IF/Switch formula.

Topic Labels: Formulas
Solved
Jump to Solution
440 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Kcross
5 - Automation Enthusiast
5 - Automation Enthusiast

Kcross_0-1712075027751.png

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")))
1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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()
)

 

 

See Solution in Thread

1 Reply 1
kuovonne
18 - Pluto
18 - Pluto

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()
)