Help

Nesting Multiple IF statements in a formula

Topic Labels: Formulas
909 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Travis_Groom1
6 - Interface Innovator
6 - Interface Innovator

Hi everyone,

Trying to write a multiple If statement formula to return in what quarter something took place.

Example:
IF(
AND(
IS_AFTER(
{Training Start Date},
‘2020-03-29’
),
IS_BEFORE(
{Training Start Date},
‘2020-07-04’
)
),
“FY21Q1”
,

This formula works fine, but when I try to add more modifiers for each quarter, it doesn’t seem to work.

IF(
AND(
IS_AFTER(
{Training Start Date},
‘2020-03-29’
),
IS_BEFORE(
{Training Start Date},
‘2020-07-04’
)
),
“FY21Q1”
,

IF(
AND(
IS_AFTER(
{Training Start Date},
‘2020-07-05’
),
IS_BEFORE(
{Training Start Date},
‘2020-10-03’
)
),
“FY21Q2”
)
IF(
AND(
IS_AFTER(
{Training Start Date},
‘2020-02020-10-04’
),
IS_BEFORE(
{Training Start Date},
‘2021-01-02’
)
),
“FY21Q3”
)
IF(
AND(
IS_AFTER(
{Training Start Date},
‘2021-01-03’
),
IS_BEFORE(
{Training Start Date},
‘2021-04-21’
)
),
“FY21Q4”
)
IF(
AND(
IS_AFTER(
{Training Start Date},
‘2021-04-04’
),
IS_BEFORE(
{Training Start Date},
‘2021-07-03’
)
),
“FY22Q1”
)

Hopefully, someone can see what I am missing that is causing it not to do the multiple steps.

Thanks!

2 Replies 2

Are your quarters at reliable break points each year? Are you going to have to edit this formula for each successive quarter every year from now on?

Also your formula doesn’t work because they aren’t nested IF statements. A nested IF looks essentially like

IF(1, 2, IF(1, 2, IF(1, 2, 3)))
Travis_Groom1
6 - Interface Innovator
6 - Interface Innovator

Sadly not at super reliable break points. I would have to add each quarter in manually, however if I build it right once, I will likely copy and paste this formula a lot.

However, the nesting correctly seemed to work!

Thanks for the tips.