Oooo…
I don’t know how I missed that last portion of the formula!
If you wanted to add the last layer to account for the + 4 weeks, then it would look like this:
IF(
AND(
{⛔ Extender Weeks},
{👌 WEEKS in Program (26 = 6mo)}
),
IF(
{⛔ Extender Weeks} = 13,
{👌 WEEKS in Program (26 = 6mo)} + 13,
IF(
{⛔ Extender Weeks} = 9,
{👌 WEEKS in Program (26 = 6mo)} + 9,
IF(
{⛔ Extender Weeks} = 4,
{👌 WEEKS in Program (26 = 6mo)} + 4
)
)
),
"⚠ Missing Key Data!"
)
Now, regarding the AND function as the conditional, nothing really changes beyond the fact that we’ve just added a new layer to the IF nesting.
It’s possible that I’ve misread your question about the AND statement, so please let me know if it went over my head.
Functionally, the formula will now just continue up the layers of 13, 9, and 4.
With that being said, I had something cross my mind when I was previously working on the original post.
Enter… My ADHD!
(I want to clarify that I tend to write things out like this because they tend to be extremely valuable for people that might go searching for help when they run into problems. So apologies if I seem like I presume you don’t know what’s happening.)
Are you using a single select field for the Extender Weeks field? Or are you using a text field?
If you’re using a single select field, then you don’t have to worry about scenarios where the Extender Weeks field will have any other value besides those three options.
However, if you’re using a text/number field, I had a slight concern about the behavior of the formula.
Now here’s a scenario…
Both fields (Extender Weeks & Weeks In) are both filled in.
However, let’s say that Extender Weeks, despite being filled in, doesn’t have any of 13,9, or 4.
If this happens, the formula will just return null/blank.
This happens because the formula executed the truthy block, which is the nested IF statements.
But if none of the nested IF statements return true, then at the end of the last IF (the one that checks if it equals 4), the formula does nothing because we haven’t defined any actions for falsey conditions.
The syntax is written as:
IF(
{Condition 1},
{Action if Truthy},
{Action if Falsey}
)
But something about how IF statements generally work, is that the Falsey block is actually optional.
You can think of this as…
IF(
{Condition 1},
{Action if Truthy}
)
In this example, if the condition returns falsey, then it will just return nothing.
If you look at the formula I provided you and pull apart the first action, you have three IF statements.
Each of them has a condition and an action if the conditions returns true.
However, none of them have a falsey block, so if Extender Weeks = 2, then it will just return blank.
So, let’s say you want to create some sort of message that lets you know something ain’t right.
In order to do this, you can use this formula structure:
1 IF(
2 AND(
3 {⛔ Extender Weeks},
4 {👌 WEEKS in Program (26 = 6mo)}
5 ),
6 IF(
7 {⛔ Extender Weeks} = 13,
8 {👌 WEEKS in Program (26 = 6mo)} + 13,
9 IF(
10 {⛔ Extender Weeks} = 9,
11 {👌 WEEKS in Program (26 = 6mo)} + 9,
12 IF(
13 {⛔ Extender Weeks} = 4,
14 {👌 WEEKS in Program (26 = 6mo)} + 4,
15 "Example Message!"
16 )
17 )
18 ),
19 "⚠ Missing Key Data!"
20 )
Everything is the same here, except for that very last IF statement.
Here it is without all the noise:
IF(
{⛔ Extender Weeks} = 4,
{ 👌 WEEKS in Program (26=6mo)} + 4,
"Example Message!"
)
Here, if Extender Weeks isn’t 4, then the formula will return the Example Message! text.
It looks like this:
Just food for thought and me being bored.
Again, let me know if you have any questions or think I’m insane and scratching at the walls.
They’re pretty great about that too.
Missing Key Data!” message at the end of the formula. If I take that last bit out, I get nothing returned for results in the field at all. 