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.
Thanks.
Amy
Answers
-
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
Categories
Check out the Formula Handbook template!