COUNTIF date is within 30 days -- not calculating correctly

Kayla (Holbrook) Sweeney

https://app.smartsheet.com/b/publish?EQBCT=3fb03d986a05405a904c0f9b5c317049

In the sheet summary, I am trying to count if the date in the date columns falls within the next 30 days. It's working fine for Date 1 column, but not for Date 2 column (or the copy of date 2 I created to verify).

This is my formula: =COUNTIF([Date 2]:[Date 2], <=TODAY(30))

It's calculating a number, I am not getting an error, but the calculation is not accurate. I have built filters and checked the dates manually and it's not adding up at all!

What could be causing this issue?

Kait Bankey

=COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-30)))

Kait Bankey

Kayla,

Great question! I'd be happy to help.

Can you try updating to this?

=COUNTIF([Date 2]:[Date 2],  <= TODAY(-30))

Kayla (Holbrook) Sweeney

@kbankey77181 -- That did not work. If I do that formula, I get a total of 7 dates counted (the actual count number should be 15 dates).

I've tried reversing the carrot symbol, removing the =, doing +30, -30, refreshing, new date columns, etc. Nothing is working.

Experts:

@Paul Newcome

@Andrée Starå

Kait Bankey

=COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-30)))

John Jonassen

@Kait Bankey is correct in their reply, including the AND, since what you are really looking for is the count of Date Requesteds BETWEEN 30 days ago and today.

Kayla (Holbrook) Sweeney

@Kait Bankey - YES! I had to flop around some of the carrots and remove the - before the 30 (this is what I ended with: =COUNTIF([Date 2]:[Date 2], AND(@cell >= TODAY(), @cell <= TODAY(30)))

I wanted to count upcoming tasks in 30 days (not in the last 30 days)

WORKED!

THANK YOU!

95766

What if I wanted to modify this formula to check a box rather than COUNTIF?

Genevieve P.

Hi @95766

If you're looking at this on a row-by-row basis, try something like this:

=IF(AND(Date@row >= TODAY(), Date@row <= TODAY(30)), 1, 0)

Cheers,

Genevieve