CountIf Day is between Start and Due Date

Ana Guilherme

I'm stumped in this formula...

I wish to perform a countif when a date is between two dates.

I have a Start Date and a Due Date Column and in another sheet I have a list of days.

and I want to count how many times the day June 07th is between Start Date and Due Date

In the case below June 07th should be counted as 1, June 09th should be counted as 2

I've tried with countifs, index, collect, etc, but I'm not getting there :(

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Mark Safran

Hi @Ana Guilherme

A COUNTIFS( ) should work if you make [Start Date] your first range, [Due Date] your second range, and just use greater than & less than comparisons against your query date as the criterion for both.

Try something like this (where [Query Date] is the name of the column where your 6/7 through 6/20 values are living):

=COUNTIFS([Start Date]:[Start Date], <=[Query Date]@row, [Due Date]:[Due Date], >=[Query Date]@row)

-MCS

All comments

Mark Safran

Hi @Ana Guilherme

A COUNTIFS( ) should work if you make [Start Date] your first range, [Due Date] your second range, and just use greater than & less than comparisons against your query date as the criterion for both.

Try something like this (where [Query Date] is the name of the column where your 6/7 through 6/20 values are living):

=COUNTIFS([Start Date]:[Start Date], <=[Query Date]@row, [Due Date]:[Due Date], >=[Query Date]@row)

-MCS

Ana Guilherme

@Mark Safran Thank you!

Worked perfectly :)

Owen TP

I'm trying to use the formula above and simply do not understand the Query date. Is this a helper column added or referencing the existing dates?

Ana Guilherme

Hello @Owen TP,

No.

Query Date should be substitute by the date you wish to count.

following the example I gave (in my first post):

Query Date can be June 07th.

Example:

=COUNTIFS({Start Date}, <=(June 07th), {Due Date}, >=(June 07th))

Replace June 07th by the cell date that you wish to count

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.