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")))

Find more posts tagged with

Accepted answers

Carson Penticuff

You have "November" inside the second AND function instead of following it.

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.

All comments

Carson Penticuff

You have "November" inside the second AND function instead of following it.

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.

CaseyG2B

https://community.smartsheet.com/discussion/comment/403114#Comment_403114

You're exactly right, thank you! That fixed it!!

Carson Penticuff

I'm glad it worked. It's very easy to miss or misplace a parenthesis when the formula starts to get complicated! 👍

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.