Skip to main content
Solved

Formula based on Single Select

A
Forum|alt.badge.img+3

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?

Best answer by Alexey_Gusev

Ashley_B wrote:

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


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

View original
Did this topic help you find an answer to your question?

6 replies

H
Forum|alt.badge.img+4
  • Harsh2
  • Participating Frequently
  • 23 replies
  • November 22, 2023

Hi @Ashley_B 

Try using the formula

IF({Year Opt In}='yes', DATEADD(Tenure, 1, 'years'), " ")
A
Forum|alt.badge.img+3
  • Ashley_B
  • Author
  • New Participant
  • 3 replies
  • November 22, 2023
Harsh2 wrote:

Hi @Ashley_B 

Try using the formula

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

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

S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • November 22, 2023

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

 

A
Forum|alt.badge.img+3
  • Ashley_B
  • Author
  • New Participant
  • 3 replies
  • November 23, 2023
Sho wrote:

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

 


@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
Forum|alt.badge.img+23
  • Alexey_Gusev
  • Brainy
  • 1152 replies
  • Answer
  • November 23, 2023
Ashley_B wrote:

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


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

S
1 person likes this
A
Forum|alt.badge.img+3
  • Ashley_B
  • Author
  • New Participant
  • 3 replies
  • November 27, 2023
Alexey_Gusev wrote:

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


🤦🏻‍♀️ Thank you @Alexey_Gusev 

Alexey_Gusev
1 person likes this
  • Alexey_Gusev
    Alexey_Gusev

Reply


Most helpful members this week

Terms & Conditions