Help

Adding Years To A Set Date Based On Single Select

Topic Labels: Formulas
Solved
Jump to Solution
1222 2
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Owens
5 - Automation Enthusiast
5 - Automation Enthusiast

So I know this shouldn’t be all that difficult, and I get the results for my first two outcomes (Blank and 1 Year), but no years past Year 1 provide an answer.

IF({Last Service Date}=BLANK(),BLANK(),IF({Service Frequency}=“1 Year”,DATEADD({Last Service Date}, 365, ‘days’,IF({Service Frequency}=“1.5 Years”,DATEADD({Last Service Date}, 547, ‘days’,IF({Service Frequency}=“2 Years”,DATEADD({Last Service Date}, 730, ‘days’, IF({Service Frequency}=“3 Years”,DATEADD({Last Service Date}, 1095, ‘days’, IF({Service Frequency}=“4 Years”,DATEADD({Last Service Date}, 11460, ‘days’)))))))))))

“Last Service Date” is a Calendar Type and “Service Frequency” is a Single Select Type (Single Line Text had same result). In Excel I can use a different formula that works just fine, but is not recognized here

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

Hi @David_Owens - you can use a nested IF for this formula, but it you SWITCH statement instead it is a little easier to read (and to debug). I would do something like:

IF(
  AND({Last Service Date}, {Service Frequency}), 
  SWITCH(
    {Service Frequency},
    '1 year', DATEADD({Last Service Date}, 365, 'days'),
    '1.5 years', DATEADD({Last Service Date}, 547, 'days'),
    '2 years', DATEADD({Last Service Date}, 730, 'days')
  )
) 

The AND() at the start is just saying “if Last Service Date and Service Frequency exist” - this is a bit easier than saying “if X = blank, then blank” etc.

JB

See Solution in Thread

2 Replies 2
JonathanBowen
13 - Mars
13 - Mars

Hi @David_Owens - you can use a nested IF for this formula, but it you SWITCH statement instead it is a little easier to read (and to debug). I would do something like:

IF(
  AND({Last Service Date}, {Service Frequency}), 
  SWITCH(
    {Service Frequency},
    '1 year', DATEADD({Last Service Date}, 365, 'days'),
    '1.5 years', DATEADD({Last Service Date}, 547, 'days'),
    '2 years', DATEADD({Last Service Date}, 730, 'days')
  )
) 

The AND() at the start is just saying “if Last Service Date and Service Frequency exist” - this is a bit easier than saying “if X = blank, then blank” etc.

JB

Awesome! That did it. Thank you very much!