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
Check out the Formula Handbook template!
