Check if a moratorium date falls on the range between Start Date and End Date

I have a list of moratorium dates. I want to confirm if the Start to End Date for each task range falls on a moratorium date. I used this formula and it's unparseable.

=COUNTIF([Moratorium Date]:[Moratorium Date], >=[Start Date]@row) AND COUNTIF([Moratorium Date]:[Moratorium Date], <=[End Date]@row)

When I test the first COUNTIF function it works, but doesn't seem to work if I add the AND second argument.

Find more posts tagged with

Formulas

Countif

Accepted answers

All comments

Joseph Aloysias - vSaaS Global

@Baltimorejones You can use the IF and AND functions together to check if the dates fall within a specified range. Please try the formula below:

=IF(AND([Moratorium Date]@row >= [Start Date]@row, [Moratorium Date]@row <= [End Date]@row), "Yes", "No")

Please let me know if you need any assistance!

Baltimorejones

Hi @Joseph Aloysias - vSaaS Global This is really helpful; thanks! The moratorium date field is a column that links to a separate sheet that lists all of the moratorium dates. Therefore I want to check the Start and End Date range of a row against the entire column of Moratorium dates. How can I expand this to check the start and end date range of a row against the entire column of moratorium dates?

Example: Start Date is 6/23 and end date is 6/27.

Moratorium dates for june include: 6/1, 6/2, 6/3, etc.

Save Draft

Paul Newcome

Try a COUNTIFS

=COUNTIFS([Moratorium Date]:[Moratorium Date], >=[Start Date]@row, [Moratorium Date]:[Moratorium Date], <=[End Date]@row)

If the Moratorium Dates are in a separate sheet, you will need to use a {Cross Sheet Reference}.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.