# Formula based on Single Select

Topic Labels: Formulas
Solved
1020 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?
1 Solution

Accepted Solutions
12 - Earth

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'...

6 Replies 6
6 - Interface Innovator

Try using the formula

``IF({Year Opt In}='yes', DATEADD(Tenure, 1, 'years'), " ")``
5 - Automation Enthusiast

@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?

11 - Venus

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')
)``````

5 - Automation Enthusiast

@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.

12 - Earth

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'...

5 - Automation Enthusiast

🤦🏻‍♀️ Thank you @Alexey_Gusev