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

Eric Tronson

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.

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())

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.