Formula to identify whether or not a project starts or ends in a Quarter

Hello,

I have columns for each quarter of the year (Q1, Q2, etc.) in Checkbox format. I am trying to create a formula where the Checkbox is automatically checked if the Start Date and/or End Date fall within the quarter.

For Q1, I only need to look at the start date because all of the Start Dates are in 2022.

=IF(AND([Start Date]@row >= DATE(2022, 1, 1), [Start Date]@row <= DATE(2022, 3, 31)), true, false)

I run into trouble for the Q2 where the project may have started in Q1 but will run into Q2, so I need to consider the End Date as well. I tried the following as well as other variations but I get the #incorrect argument error.

=IF((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), true, IF(AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30))), true, false)

Please let me know if I need more or less parenthesis, or if I need an OR thrown in there. Thank you in advance.

Find more posts tagged with

Accepted answers

Pamelam

OK, l solved it and am just leaving this for others if they need it. Instead of putting the End Date in the False section, I used OR

=IF(OR((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), (AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30)))), true, false)

All comments

Pamelam

Almost as soon as I posted this, I solved half of my problem. I added a second IF at the beginning. This works for the rows that have Start and End Dates completely within Q2, but any rows that start and/or end outside of Q2 still give the #Incorrect Argument.

=IF(IF(AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), true, IF(AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30))), true, false)

Again, thank you for any assistance.

Pamelam

OK, l solved it and am just leaving this for others if they need it. Instead of putting the End Date in the False section, I used OR

=IF(OR((AND([Start Date]@row >= DATE(2022, 4, 1), [Start Date]@row <= DATE(2022, 6, 30))), (AND([End Date]@row >= DATE(2022, 4, 1), [End Date]@row <= DATE(2022, 6, 30)))), true, false)

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.