How To: Formula to count if Today() falls between two dates?

I am trying to calculate the count of approved entries for shrinkage based on out of office request. There may be other things needed.

Range 1 is "Start Date"

Range 2 is "End Date"

Range 1 and 2 are "Date" fields

=COUNTIFS({Intrusion Non Pre-Approved Attendance Range 1}, >=TODAY(), {Intrusion Non Pre-Approved Attendance Range 2}, <=TODAY())

I use "Today()" function for todays date; If the leave request Start and End are the same day is can get the count just fine. If the start date is 5/28/2022 and end date is 6/15/2022 I get 0 for a count. In the example below I have 2 entries but only 1 count.

Find more posts tagged with

Accepted answers

Jeff Reisman

@Eric Tronson D'oh! You have your operator signs backwards. You want the Start date to be LESS THAN or equal to today, and your End Date to be GREATER than or equal to today.

=COUNTIFS({Intrusion Non Pre-Approved Attendance Range 1}, @cell <= TODAY(), {Intrusion Non Pre-Approved Attendance Range 2}, @cell >= TODAY())

All comments

Jeff Reisman

@Eric Tronson

Add @cell to your criteria.

=COUNTIFS({Intrusion Non Pre-Approved Attendance Range 1}, @cell >= TODAY(), {Intrusion Non Pre-Approved Attendance Range 2}, @cell <= TODAY())

Eric Tronson

https://community.smartsheet.com/discussion/comment/331676#Comment_331676

This still returns a count of 1 not 2.

Jeff Reisman

@Eric Tronson D'oh! You have your operator signs backwards. You want the Start date to be LESS THAN or equal to today, and your End Date to be GREATER than or equal to today.

=COUNTIFS({Intrusion Non Pre-Approved Attendance Range 1}, @cell <= TODAY(), {Intrusion Non Pre-Approved Attendance Range 2}, @cell >= TODAY())

Eric Tronson

https://community.smartsheet.com/discussion/comment/331685#Comment_331685

Ok..... Now I feel dumb. Thank you tons! This seems to be working.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.