NESTED IF STATEMENT
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
Best Answer
-
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
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
Hi Amy,
I'm glad you were able to figure it out! Thanks for explaining your goals & providing the final solution, looks good!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!