NESTED IF STATEMENT

Amy Evans

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

Amy Evans

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

Genevieve P.

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

Amy Evans

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

Genevieve P.

Hi Amy,

I'm glad you were able to figure it out! Thanks for explaining your goals & providing the final solution, looks good!