Skip to main content

Nested If Statements for renewal dates

  • July 17, 2019
  • 5 replies
  • 41 views
C

Hi there!

I’m new to AirTable and am struggling with building an IF statement to populate a renewal date. We have annual, 3-year and lifetime memberships, so I’m trying to create a formula that will populate our renewal date field based on the membership type duration.

While I need to get to the nested IF statement for all of my memnership types, I can’t get a single formula to work. Any help would be much appreciated. Here’s what I’ve tried (and many variations).

If ({M_MembershipType} = “Senior/Sr. Family Annual”, DATEADD ({M_TransactionDate}, +365, ‘days’))

    5 replies

    JonathanBowen
    Forum|alt.badge.img+18

    HI @Colleen_Malloy - you might find a SWITCH() formula to be easier to handle and works well in these situations (where you want a result based on one variable having a number of values). Try this:

    SWITCH(
  Type, 
  'Annual', DATEADD(Started, 1, 'years'), 
  '3-Year', DATEADD(Started, 3, 'years'), 
  'Lifetime', DATEADD(Started, 100, 'years')
)

    (Note that “100 Years” has been used as a proxy for “lifetime”).

    JB

    C
    • Colleen_Malloy
    • Author
    • New Participant
    • July 18, 2019

    Thanks so much for the advice! I’m getting an error with my formula and can’t figure out where I’ve gone wrong. Any ideas?

    SWITCH(

    M_MembershipType,

    'Senior/Sr. Family Annual’, DATEADD(M_TransactionDate, 1, ‘years’),

    ‘Senior/Sr. Family 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),

    ‘Regular/Family Annual’, DATEADD(M_TransactionDate, 1, ‘years’),

    ‘Life’, DATEADD(M_TransactionDate, 100, ‘years’)

    ‘Regular/Family 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),
    ‘Student (under 21) Annual’, DATEADD(M_TransactionDate, 1, ‘years’),
    ‘Student (under 21) 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),
    ‘Organizational Annual’, DATEADD(M_TransactionDate, 1, ‘years’),
    ‘Organizational 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),
    ‘Complimentary’, DATEADD(M_TransactionDate, 1, ‘years’),
    ‘Supporting Annual’, DATEADD(M_TransactionDate, 1, ‘years’),
    )

    A
    Forum|alt.badge.img+19
    • AlliAlosa
    • Inspiring
    • July 18, 2019

    Thanks so much for the advice! I’m getting an error with my formula and can’t figure out where I’ve gone wrong. Any ideas?

    SWITCH(

    M_MembershipType,

    'Senior/Sr. Family Annual’, DATEADD(M_TransactionDate, 1, ‘years’),

    ‘Senior/Sr. Family 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),

    ‘Regular/Family Annual’, DATEADD(M_TransactionDate, 1, ‘years’),

    ‘Life’, DATEADD(M_TransactionDate, 100, ‘years’)

    ‘Regular/Family 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),
    ‘Student (under 21) Annual’, DATEADD(M_TransactionDate, 1, ‘years’),
    ‘Student (under 21) 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),
    ‘Organizational Annual’, DATEADD(M_TransactionDate, 1, ‘years’),
    ‘Organizational 3 Year’, DATEADD(M_TransactionDate, 3, ‘years’),
    ‘Complimentary’, DATEADD(M_TransactionDate, 1, ‘years’),
    ‘Supporting Annual’, DATEADD(M_TransactionDate, 1, ‘years’),
    )


    Hi Colleen,

    Looks like there’s just a couple misplaced commas in your formula. You don’t need the last comma (after “Supporting Annual”) and you’re missing one after the line with “Life”.

    SWITCH(
M_MembershipType,
'Senior/Sr. Family Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Senior/Sr. Family 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Regular/Family Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Life', DATEADD(M_TransactionDate, 100, 'years'),
'Regular/Family 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Student (under 21) Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Student (under 21) 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Organizational Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Organizational 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Complimentary', DATEADD(M_TransactionDate, 1, 'years'),
'Supporting Annual', DATEADD(M_TransactionDate, 1, 'years')
)
    C
    • Colleen_Malloy
    • Author
    • New Participant
    • July 18, 2019

    Hi Colleen,

    Looks like there’s just a couple misplaced commas in your formula. You don’t need the last comma (after “Supporting Annual”) and you’re missing one after the line with “Life”.

    SWITCH(
M_MembershipType,
'Senior/Sr. Family Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Senior/Sr. Family 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Regular/Family Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Life', DATEADD(M_TransactionDate, 100, 'years'),
'Regular/Family 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Student (under 21) Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Student (under 21) 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Organizational Annual', DATEADD(M_TransactionDate, 1, 'years'),
'Organizational 3 Year', DATEADD(M_TransactionDate, 3, 'years'),
'Complimentary', DATEADD(M_TransactionDate, 1, 'years'),
'Supporting Annual', DATEADD(M_TransactionDate, 1, 'years')
)

    That works. Thanks!!

      Justin_Barrett
      Forum|alt.badge.img+21

      Just to throw another option out there, you might consider placing the SWITCH inside the DATEADD, as the only thing you really need to change is the number of years. Here’s the formula with that alteration:

      DATEADD(
    M_TransactionDate, 
    SWITCH(
        M_MembershipType,
        'Senior/Sr. Family Annual', 1,
        'Senior/Sr. Family 3 Year', 3,
        'Regular/Family Annual', 1,
        'Life', 100,
        'Regular/Family 3 Year', 3,
        'Student (under 21) Annual', 1,
        'Student (under 21) 3 Year', 3,
        'Organizational Annual', 1,
        'Organizational 3 Year', 3,
        'Complimentary', 1,
        'Supporting Annual', 1
    ), 'years'
)
        Terms & ConditionsAccessibility statement