Skip to main content

Need Help with IF and Switch Formula

P
Forum|alt.badge.img+3
Hello,
 
I am having issues with adding additional advertising week options to the formula below. This formula works well:
IF(AND({Advertising Plan}, {Live Date}),SWITCH( {Advertising Plan},"4 weeks", DATEADD({Live Date}, 27, 'days'),"52 weeks", DATEADD({Live Date}, 364, 'days')))
 
But as soon as I try to add additional week options (I have already added them to the Advertising Plan single select field), I get the 'formula not valid warning'. This is what I am putting in:
IF(AND({Advertising Plan}, {Live Date}),SWITCH({Advertising Plan},"2 weeks", DATEADD({Live Date}, 13, 'days'){Advertising Plan},"4 weeks", DATEADD({Live Date}, 27, 'days'),"8 weeks"({Live Date}, 27, 'days',"52 weeks", DATEADD({Live Date}, 364, 'days' )))
 
Please let me know where my formula is mucking me up, I feel I am going cross-eyed after looking at it for so long.
 
Thanks
P

3 replies

G
Forum|alt.badge.img+13
  • Grunty
  • Inspiring
  • 69 replies
  • October 18, 2023

Hey, what kind of Switch is that ..!?

You're repeating the switching field {Advertising Plan} on every option.

I guess even the formula parser is getting cross-eyed. 👀

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

Hi @Penny_Bell ,

It's easier to read like this, with line breaks and tabs for clarity.
Is 8 weeks 55 days right?

IF( AND({Advertising Plan}, {Live Date}), SWITCH( {Advertising Plan}, "2 weeks", DATEADD({Live Date}, 13, 'days'), "4 weeks", DATEADD({Live Date}, 27, 'days'), "8 weeks", DATEADD({Live Date}, 55, 'days'), "52 weeks", DATEADD({Live Date}, 364, 'days') ) )

 

P
Forum|alt.badge.img+3
  • Penny_Bell
  • Author
  • Inspiring
  • 7 replies
  • October 23, 2023
Grunty wrote:

Hey, what kind of Switch is that ..!?

You're repeating the switching field {Advertising Plan} on every option.

I guess even the formula parser is getting cross-eyed. 👀


Yes, I need a switch on every option. If there is a better way to do it please let me know.

 

Basically, the Advertising Plan needs to be one day less than the specific number of weeks. So 2 weeks is actually 14 days, etc etc

Reply


Most helpful members this week

Terms & Conditions