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
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!