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.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!