Counting Dates within a range

I am trying to get a count of the number of rows with dates that meet one or another condition. I know I cant use Countif so I planned on adding a column next to my date column to capture the data.

One condition is that the date is either within the next 30 days OR the date is in the past.

Here is what I have so far and it returns #UNPARSEABLE ( I see that error so much, I am planning on naming my next dog 'UNPARSEABLE)

=IF(OR[Survey Next Due Date]1 <= Today(+30), [Survey Next Due Date]1 < TODAY(0)), "1"))

Find more posts tagged with

Formulas

Comments

L_123

Well... there's a couple things to note on this.

1. you don't need multiple criteria

=countif(range,@cell <= today(30))

will count all dates less than or equal to 30 days from now. that means it will count the dates in the past as well.

2. you can use multiple criteria with a countifs

=countifs(range,or(@cell <= today(30),@cell < today()))

is a perfectly viable if redundant formula.

L_123

To make your if statement work it should look like this

=IF(OR([Survey Next Due Date]1 <= TODAY(30), [Survey Next Due Date]1 < TODAY(0)), 1)

Your issue was with parenthesis. Everything else worked fine, though it is redundant as the second statement is covered by the first.

Mike Wilday

Hi Phil, have you considered using Countifs? You can set multiple criteria and count your results.

=COUNTIFS([Date Range]:[Date Range], <TODAY()) + COUNTIFS([Date Range]:[Date Range], >=TODAY(30))

Replace the Date Range with your own column. That will add together both counts.

Phil B.

Thanks for the help, All your suggestions worked well. It also helped me spot that I had bad data in some cells and that was causing failures as well. Thanks.

Mitch@Enmach

Hello,

I stumbled across this thread as I am trying to do something similar.

=countif(range,@cell <= today(30))

I have tried using the above formula you suggested to count many dates in a column that are older than four days from today's date.

Having no luck, I know I am doing something slightly wrong, just can't work out what.

Thanks in advance

Sign in to join the conversation!

Quick Links

Trending discussions

Using a date range to define a planned Sprint name
I want to use a Start Date to determine which sprint a ticket will be identified with. For example Sprint 17 starts on 08/13/25 and ends on 08/27/25. If my Start Date is within this range then I want to output the Sprint Name into a Planned Sprint column. =IF(([Start Date]@row >= "8/13/25", IF(AND)([Start Date]@row <=…
Question about automation to generate duration of each status
Can we get an automation to generate how long is the duration of the open, closed, and average days that each open status have been open for before a status changed? Also can we get an automated count of days that the item has been open or until the status changed to closed?
Cross-sheet Copy and then INDEX/MATCH Help Needed
I am working on developing an End to End Testers sheet and a Defect Sheet. When a scenario test has resulted in a Defect Confirmed, I would like the Scenario ID, Scenario Name, and the Scenario Description to be pulled into the Defect Sheet. My initial approach is to break this information into 2 cross-sheet formulas.…