How can I count dates in the past/future?

Hi,

I'm trying to create a formula that counts certain actions in a column based on their date. Say, if there are three dates: 5/15/2020, 5/20/2020, and 5/30/2020, I want to be able to know which ones are in the future and which ones are in the past. In this case, the counter should return 2 dates in the past and 1 date in the future.

Any help is appreciated! Thanks so much in advance

Find more posts tagged with

Sheets

Formulas

Support

Solution Center

functionality

Account and User Management

Accepted answers

Paul Newcome

Is your date column actually a date type column? If it is, how are your dates being populated? Which one is returning a zero, or is it both of them?

All comments

Paul Newcome

In the past would be...

=COUNTIFS([Date Column]:[Date Column], @cell < TODAY())

Today or in the future would be...

=COUNTIFS([Date Column]:[Date Column], @cell >= TODAY())

Carolina Rivera

I tried this but the counter returns a zero value... not sure why though

https://community.smartsheet.com/discussion/comment/251630#Comment_251630

Paul Newcome

Is your date column actually a date type column? If it is, how are your dates being populated? Which one is returning a zero, or is it both of them?

Carolina Rivera

https://community.smartsheet.com/discussion/comment/251732#Comment_251732

I just checked my formula and it returned the values I needed, not sure how though. I'm just glad it finally worked, thanks for your help!

Paul Newcome

Happy to help! 👍️

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.