# NESTED IF STATEMENT

Options

Hello,

What am I doing incorrectly with my formula below? I'm receiving an incorrect argument error.

=IF([Team Number]28 > 0, YEAR(Date3) = "2021", MONTH(Date3), IF([Team Number]28 = 0, YEAR(\$Date\$2) = "2021", (1 + (MONTH(Date3) - MONTH(Date2))), 0), 0)

Thank you!

AE

Options

Hi Genevieve,

Thanks for your input and I worked on this problem a bit and here is where I ended up this weekend before I saw your message.

Let me also clarify the formula intent and my apologies for not including that information initially.

Situation:

• Start date may start either in 2020 or 2021
• End date may end either in 2020 or 2021
• Formula Goal: 1) How many months in 2020? 2) How many months in 2021?

Here is what I ended up with. Is there a more efficient approach?

=IF(AND(YEAR(\$Date\$2) = "2020", YEAR(\$Date\$3) = "2020"), (1 + (MONTH(\$Date\$3) - MONTH(\$Date\$2))), IF(AND(YEAR(\$Date\$2) = "2020", YEAR(\$Date\$3) = "2021"), (13 - MONTH(\$Date\$2)), IF(AND(YEAR(\$Date\$2) = "2021", YEAR(\$Date\$3) = "2021"), 0, 0)))

Thank you and I really appreciate this community of experts. Your team is AMAZING.

Amy

Options

Hi Amy,

It looks like you have 2 criteria you're searching for within each IF statement, but there's no indication of what the relation is between these 2 criteria. Let's add in an AND function to each! That way it will only run if those two criteria are both met.

You also can take out some of the parenthesis for the last "true" statement, and only need one 0 for the "false" statement.

Try this:

=IF(AND([Team Number]28 > 0, YEAR(Date3) = "2021"), MONTH(Date3), IF(AND([Team Number]28 = 0, YEAR(\$Date\$2) = "2021"), 1 + (MONTH(Date3) - MONTH(Date2)), 0))

Let me know if this works or if you have any questions!

Cheers,

Genevieve

Options

Hi Genevieve,

Thanks for your input and I worked on this problem a bit and here is where I ended up this weekend before I saw your message.

Let me also clarify the formula intent and my apologies for not including that information initially.

Situation:

• Start date may start either in 2020 or 2021
• End date may end either in 2020 or 2021
• Formula Goal: 1) How many months in 2020? 2) How many months in 2021?

Here is what I ended up with. Is there a more efficient approach?

=IF(AND(YEAR(\$Date\$2) = "2020", YEAR(\$Date\$3) = "2020"), (1 + (MONTH(\$Date\$3) - MONTH(\$Date\$2))), IF(AND(YEAR(\$Date\$2) = "2020", YEAR(\$Date\$3) = "2021"), (13 - MONTH(\$Date\$2)), IF(AND(YEAR(\$Date\$2) = "2021", YEAR(\$Date\$3) = "2021"), 0, 0)))

Thank you and I really appreciate this community of experts. Your team is AMAZING.

Amy