Counting Dates within a range

Phil B.

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"))

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