IF(AND for Date Range
Thanks in advance to anyone who offers help.
I'm using the following formula to automatically group line items into the month they belong to (this is calculated from a "Modified Date" column which is automatically updated).
It's working fine for "October" but not for "November" and I can't figure out why. I've tried separating the clauses and I still get "Incorrect Argument" when calculating for November. Any ideas?
Formula:
=IF(AND([Modified Date]@row >= DATE(2023, 9, 30), [Modified Date]@row <= DATE(2023, 11, 1)), "October", IF(AND([Modified Date]@row >= DATE(2023, 10, 31), [Modified Date]@row <= DATE(2023, 12, 1), "November")))
Best Answer
-
You have "November" inside the second AND function instead of following it.
=IF(AND([Modified Date]@row >= DATE(2023, 9, 30), [Modified Date]@row <= DATE(2023, 11, 1)), "October", IF(AND([Modified Date]@row >= DATE(2023, 10, 31), [Modified Date]@row <= DATE(2023, 12, 1), "November")))
An additional note: Using ">=" and "<=" will include the listed date. 09/30 and 11/01 will count as October. Not sure if this was your intention.
Answers
-
You have "November" inside the second AND function instead of following it.
=IF(AND([Modified Date]@row >= DATE(2023, 9, 30), [Modified Date]@row <= DATE(2023, 11, 1)), "October", IF(AND([Modified Date]@row >= DATE(2023, 10, 31), [Modified Date]@row <= DATE(2023, 12, 1), "November")))
An additional note: Using ">=" and "<=" will include the listed date. 09/30 and 11/01 will count as October. Not sure if this was your intention.
-
You're exactly right, thank you! That fixed it!!
-
I'm glad it worked. It's very easy to miss or misplace a parenthesis when the formula starts to get complicated! 👍
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.2K Get Help
- 384 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 130 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!