How do I count how many rows where today falls between the Start Date and the End Date?

SembraMartin

I'm trying to count how many rows of the sheet fall between the Start Date and the End date.

I've tried various formulas, including ones that I've found online. This is the one I'm currently trying and its coming up as #UNPARSEABLE. Can someone tell me what I'm doing wrong? I'd like to use this formula in a summary sheet.

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

Data set.JPG

Paul Newcome

The problem is the ranges. You need to use [Column Name]:[Column Name] to reference an entire column.

=COUNTIFS([Start Date]:[Start Date], @cell<=TODAY(), [End Date]:[End Date], @cell>= TODAY())

Razetto

@SembraMartin Have you tried using @cell instead? COUNTIFS([START Date], @cell <= TODAY(), [END Date], @cell >= TODAY())

SembraMartin

@Razetto Yes, I have. It comes up with the same #UNPARSEABLE error.

Razetto

@SembraMartin I used a small sample of your data and plug the formula with @cell in the summary sheet (text/number field) and got a 2 so I'm not sure where the problem is.

Cory Page

Hello, I tried using the formula above but things got a bit more tricky than I think they needed to. This probably isn't exactly what you're looking for but I like to keep things as simple as possible and still meet my needs so here is an option for you.

in column "Fall between the Start Date and the End date" I just used an if statement

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

then I created a space at the top of my sheet and used a simple Count if

=COUNTIF([fall between the Start Date and the End date.]2:[fall between the Start Date and the End date.]16, "Yes")

I like filters and this way you have a filter, you can rename the "Yes" or "No" to whatever you want.

Just in case you wanted the total count to display on every row:

=[fall between the Start Date and the End date.]$1

^Don't for get the $ sign before the 1 though^

In your example you already had "Today's" date so there is no need to use "Today()" in the formula. Hopefully you find this useful if not feel free to reply and I will try a different method figured I would start with the easiest one first.

Paul Newcome

The problem is the ranges. You need to use [Column Name]:[Column Name] to reference an entire column.

=COUNTIFS([Start Date]:[Start Date], @cell<=TODAY(), [End Date]:[End Date], @cell>= TODAY())

SembraMartin

I got it! thanks everyone!

Paul Newcome

Happy to help. 👍️