Formula to change timezone based on single-select ...

[Ashley_B]

Hello - 

Situation: Volunteers on the team have a 2-year tenure and when those 2 years are up, they can opt-in for 1 more year. My table shows their "start date" and then I have a column showing their "end date" using this formula 

DATEADD({Current WON Role Start Date}, 2, 'years') and have an automation that alerts me 60 days before that end date. 

Need: What I'd like to happen then is after the Opt-In option is discussed and if the volunteer decides to add 1 more year to their volunteer work, a formula runs that gives a new end date. 

Based on: What that said, I'd want the formula to run only if YES is selected from a drop down. Then I would assume we can take the tenure date (original end date) and add 1 year to spit out a new end date. 

I'm not great at formulas and tried - IF({1+ Year Opt In}='yes', DATEADD(Tenure, 1, 'years'), " ")

It doesn't give me an error but also doesn't populate a new date. Any insight?

[Harsh2]

Hi @Ashley_B 

Try using the formula

IF({Year Opt In}='yes', DATEADD(Tenure, 1, 'years'), " ")

[Ashley_B]

@Harsh2 I inserted the formula and didn't get an error message (aka the formula was accepted) but it still doesn't generate a new date if the drop down is Yes. Any other thoughts?

[Sho]

Hi @Ashley_B 

Wouldn't it be like this?

IF(
  {1+ YEar Opt In}='yes',
  DATEADD({Current WON Role Start Date}, 3, 'years'),
  DATEADD({Current WON Role Start Date}, 2, 'years')
)

Does the "1+ Year Opt In" field have options other than Yes and No? If not, I think a Checkbox field would be more appropriate for the UI.
If you change it to a checkbox field, it will look like this.

IF(
  {1+ YEar Opt In},
  DATEADD({Current WON Role Start Date}, 3, 'years'),
  DATEADD({Current WON Role Start Date}, 2, 'years')
)

[Ashley_B]

@Sho I'd like to keep it as a yes/no option. If they decide not to opt-in, then I need to be alerted to start the roll off plan for them. So this will help me have a clear record. 

I tried your formula and it generated the exact same date that I have in the 'tenure' column. If I selected 'yes', the date didn't change. 

[Alexey_Gusev · Accepted solution]

Hi,
Formula expression is case-sensitive. If your single-select 'Yes' has capital letter, IF({Field}='yes'.. will be FALSE.
You should adjust the case, or do it like IF(UPPER({Field})='YES'...

[Ashley_B]

🤦🏻‍♀️ Thank you @Alexey_Gusev