Help

Nesting Multiple IF statements in a formula

Topic Labels: Formulas
682 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Travis_Groom1
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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.