Date 'is between'?

Hi, I'm wondering what's the best way to capture dates that fall between Start Date and End Date.

For instance, I'm using a COUNTIFS calculation which returns all rows with a Start Date of '>= 1/1/2021, and an End Date of '<= 31/1/2021, but that won't capture rows that start on 10/12/2020, and end on 2/2/2022; even though that work will be ongoing throughout 2021.

Any help would be very much appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

This will cover any number of years.

Using your example:

Start = 10/12/2020 and End = 2/2/2022

The formula based on my comparison logic would look like this:

=COUNTIFS(Start:Start, @cell <= DATE(2021, 1, 31), End:End, @cell >= DATE(2021, 1, 1))

Start:Start, @cell <= DATE(2021, 1, 31) = 10/12/2020 is less than 1/31/2021 which will trigger a true for the first comparison set.

End:End, @cell >= DATE(2021, 1, 1) = 2/2/2022 is greater than 1/1/2021 which will trigger a true for the second comparison set.

Since both are true then it will count as 1 in your COUNTIFS.

So using your second question:

Start = 1/4/2021 and End = 1/4/2022

The Start is less than 1/31/2021 and the end is greater than 1/1/2021, so the dates in your second question would be counted.

LisaB:-)

@Paul Newcome Paul, could you help me with something please? Based upon your formula above, I'm trying to use COUNTIFS to establish what tasks (rows) are ongoing in 2021, but the below formula returns unparseable. I can't see where it's going wrong, could you help?

(edit: 11.46, graphic replaced)

=COUNTIFS([Start Date]1:[Start Date]136), <= DATE(2021, 12, 31), [End Date]1:[End Date]136, >= DATE(2021, 1, 1))

Very many thanks, in hopeful anticipation.

Paul Newcome

You have an extra closing parenthesis after the first range that needs removed.

All comments

Paul Newcome

You are going to want to count for start dates that are less than the end of the date range and end dates that are greater than the start of the date range if that makes sense.

So you are actually going to want

Start Date of '<= 31/1/2021, and an End Date of '>= 1/1/2021

LisaB:-)

Thanks Paul. But will this show rows that start on, say, 1/4/2021 and end on 1/4/2022? I think I've solved it for myself, which is simply to focus on the end date, so '>= 1/1/2021. (Although this does return results that end in 2022, by the nature of our business, tasks take less than one year so it will have started in 2021.) Will test this and see how it goes, thanks again.

Paul Newcome

This will cover any number of years.

Using your example:

Start = 10/12/2020 and End = 2/2/2022

The formula based on my comparison logic would look like this:

=COUNTIFS(Start:Start, @cell <= DATE(2021, 1, 31), End:End, @cell >= DATE(2021, 1, 1))

Start:Start, @cell <= DATE(2021, 1, 31) = 10/12/2020 is less than 1/31/2021 which will trigger a true for the first comparison set.

End:End, @cell >= DATE(2021, 1, 1) = 2/2/2022 is greater than 1/1/2021 which will trigger a true for the second comparison set.

Since both are true then it will count as 1 in your COUNTIFS.

So using your second question:

Start = 1/4/2021 and End = 1/4/2022

The Start is less than 1/31/2021 and the end is greater than 1/1/2021, so the dates in your second question would be counted.

LisaB:-)

Yes you're right. Thanks for your excellent help as ever. Have a great weekend, Lisa.

Paul Newcome

Happy to help. 👍️

LisaB:-)

(edit: 11.46, graphic replaced)

=COUNTIFS([Start Date]1:[Start Date]136), <= DATE(2021, 12, 31), [End Date]1:[End Date]136, >= DATE(2021, 1, 1))

Very many thanks, in hopeful anticipation.

Paul Newcome

You have an extra closing parenthesis after the first range that needs removed.

LisaB:-)

@Paul Newcome Paul, I just wanted to let you know that this understanding has unlocked some critical reporting potential for our programme and will support us in forecasting ongoing. It's a watershed moment :-) Thank you.

Paul Newcome

That's great to hear!

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.