Formula Help for Dates

TailoredTouches · ‎Sep 22, 2021

This is our current working formula.

IF(AND({Number of Items}=5, {VA Recipient?}),57.39, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.18, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),88.98, IF({Number of Items}=10, 85))))))

We want to specify dates for this. So, if before October 1, 2020, then the above formula is correct.

BUT between October 1, 2020 and October 1, 2021, the formula should be:

IF(AND({Number of Items}=5, {VA Recipient?}),57.70, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.60, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),89.50, IF({Number of Items}=10, 85))))))

And then finally, starting October 1, 2021, the formula should be:

IF(AND({Number of Items}=5, {VA Recipient?}),63, IF({Number of Items}=5, 60, IF(AND({Number of Items}=7, {VA Recipient?}),78.90, IF({Number of Items}=7, 75, IF(AND({Number of Items}=10, {VA Recipient?}),94.80, IF({Number of Items}=10, 90))))))

How do I use the IS_BEFORE and IS_AFTER formula correctly to account for this situation? It’s getting quite complicated as I try to do it…

Kamille_Parks · ‎Sep 22, 2021

With some restructuring and swapping out IF()s for SWITCH()s, this isn’t too complex. For starters, your original formula can be simplified down to:

SWITCH(
   {Number of Items},
   5, IF({VA Recipient?}, 57.39, 55),
   7, IF({VA Recipient?}, 73.18, 70),
   10, IF({VA Recipient?}, 88.98, 85)
)

Since you have three “major” scenarios to look at (your date ranges), I created a nested IF() statement that outputs 1, 2, or 3 depending on where the value of the {Date Field} falls. If the field is empty, no value is returned.

IF(
   IS_BEFORE({Date Field}, "2020-10-01"), 
   1, 
   IF(
      IS_BEFORE({Date Field}, "2021-10-01"), 
      2, 
      IF({Date Field}, 3)
   )
)

That nested IF() can be wrapped inside a SWITCH() statement to make plugging in your other variable scenarios easier:

SWITCH(
   IF(IS_BEFORE({Date Field}, "2020-10-01"), 1, IF(IS_BEFORE({Date Field}, "2021-10-01"), 2, IF({Date Field}, 3))),
   1, "Formula for before 2020-10-01",
   2, "Formula for between 2020-10-01 and 2021-10-01",
   3, "Formula for 2021-10-01 onward"
)

So all together:

SWITCH(
   IF(IS_BEFORE({Date Field}, "2020-10-01"), 1, IF(IS_BEFORE({Date Field}, "2021-10-01"), 2, IF({Date Field}, 3))),
   1, SWITCH(
      {Number of Items},
      5, IF({VA Recipient?}, 57.39, 55),
      7, IF({VA Recipient?}, 73.18, 70),
      10, IF({VA Recipient?}, 88.98, 85)
   ),
   2, SWITCH(
      {Number of Items},
      5, IF({VA Recipient?}, 57.70, 55),
      7, IF({VA Recipient?}, 73.60, 70),
      10, IF({VA Recipient?}, 89.50, 85)
   ),
   3, SWITCH(
      {Number of Items},
      5, IF({VA Recipient?}, 63, 60),
      7, IF({VA Recipient?}, 78.90, 75),
      10, IF({VA Recipient?}, 94.80, 90)
   )
)

TailoredTouches · ‎Sep 24, 2021

Wow. Super, super helpful. I had not used the Switch formula before.