COUNTIF date is within 30 days -- not calculating correctly

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?

Find more posts tagged with

Formulas

Support

Accepted answers

Kait Bankey

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.