Counting rows of a date column 90 days from today's date

Victoria Ganuelas

I have tried this formula below to count the number of rows in a column that are 90 days from today's date but what it calculates does not match when I manually count the rows in the source smartsheet.

=COUNTIF({BDPM - CURRENT PROJECTS Range 5}, >(TODAY() + 90))

Mark Cronk

Hi,

Your formula looked ok but try this variation. What is the difference between your count and the formula answer?

=COUNTIF({BDPM - CURRENT PROJECTS Range 5}, @cell>TODAY(90))

Victoria Ganuelas

Hi Mark,

I added the @cell and there is still a discrepancy between what I manually count in the source sheet using the filter and what the formula calculates. The formula calculates 50 more than the manual count.

Mark Cronk

=COUNTIF({BDPM - CURRENT PROJECTS Range 5}, AND(ISDATE(@cell), @cell>TODAY(90))) will count the number of dates in the range {BDPM - CURRENT PROJECTS Range 5} that are greater than 90 days in the future from today. Your range needs to be a single date column. I added an ISDATE check to make sure you're only considering date entries.

Work this time?

Mark

Victoria Ganuelas

Still incorrect calculation Mark :(

Victoria Ganuelas

Mark, I just did a test on another sheet creating a new column with various dates. Your formula worked for 30 days, but not for 60, 90. 180 etc. Not sure where to go from here.

Mark Cronk

Alright. Not sure what's happening. Let's try a different function. Try these and see if we get lucky. The solution may help us figure out why the other formula isn't working:

=COUNTIFS({BDPM - CURRENT PROJECTS Range 5}, AND(ISDATE(@cell), @cell>TODAY(90))) 

Or

=COUNTIFS({BDPM - CURRENT PROJECTS Range 5}, ISDATE(@cell), {BDPM - CURRENT PROJECTS Range 5}, @cell>TODAY(90))

Or

=COUNTIFS({BDPM - CURRENT PROJECTS Range 5}, DATE(YEAR(@cell), Month(@cell), Day(@cell))>TODAY(90))

Or

=COUNTIFS({BDPM - CURRENT PROJECTS Range 5}, DATE(YEAR(@cell), Month(@cell), Day(@cell))+90<TODAY())

Cross your fingers.

Mark

Victoria Ganuelas

Hi Mark,

#1 and 2 yielded the same incorrect count

#3 and 4 gave an 'Invalid Data Type' error message

Mark Cronk

Thanks for continuing to work this with me. We're getting closer.

Is the range {BDPM - CURRENT PROJECTS Range 5} a single date column? In the sheet with Range 5 add a date column with the formula =[the column used in BDPM 5]@row +30.

Does it return a date 30 days in the future of the column date or an error?

If it returns an error the problem is your range.

Mark

Victoria Ganuelas

Hi Mark,

I was able to connect via phone with the help desk and for your reference, this is the formula that worked.

=COUNTIFS({BDPM - CURRENT PROJECTS Range 5}, >=TODAY(), {BDPM - CURRENT PROJECTS Range 5}, <=TODAY(90))

I appreciate all your help trying to solve! Thank you!

Mark Cronk

Excellent, So convert to COUNTIFS and confirm the date is greater than today. Good to know.

Thank you for contributing to the community.

Mark

rosegarretson

This thread helped a lot. Thanks so much!