Adding Date Criteria to formula


Good day-

I have a formula that I need to add an "end date". I am not sure that between dates are available?

Here is the formula:

=COUNTIFS({Gate1Start}, >=DATE(2020, 1, 1), {PPV}, 1, {ItemStatus}, OR(@cell = "Open", @cell = "Closed"))

The date portion needs to be BETWEEN 1/1/2020 and 12/31/2020. Right now it is just anything after 1/1/2020.





  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭

    Hi @Amy Bedard - You can use the AND function to make "between" dates. Try this!

    =COUNTIFS({Gate1Start}, AND(>=DATE(2020, 1, 1), <=DATE(2020, 12, 31)), {PPV}, 1, {ItemStatus}, OR(@cell = "Open", @cell = "Closed"))

    Alternatively, because you're looking at an entire year, you might try this:

    =COUNTIFS({Gate1Start}, YEAR(@cell) = "2020", {PPV}, 1, {ItemStatus}, OR(@cell = "Open", @cell = "Closed"))

    Please let me know if either of these work for you!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!