COUNTIFS - using a date

Lynda Ross

Hi - looking for help with what I think would be an easy formula.

What I want to do is create the number of "Complete" tasks I have that are due BEFORE 3/31/2021.

Can anyone help with my formula and point out an error? I get UNPARSEABLE as a result.

=COUNTIFS(Status2:Status571, "Complete", [Due Date2:DueDate571,<"3/31/2021")

Thanks so much!

Lynda🧐

Mark Cronk

=COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell<DATE(2021, 3, 31)))

Mark

Mark Cronk

Glad you found a solution. Happy to help. You had a couple syntax errors. I added the ISDATE to avoid getting an error.

Thanks for contributing to the Community.

Mark

Mark Cronk

Try this:

=COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell>DATE(2021, 3, 31), @cell<DATE(2021, 5, 2)))

Mark

Lynda Ross

@Mark Cronk - again, fabulous! Thank you so much! I haven't worked with many AND & ISDATE functions so I appreciate your help!

Happy St. Patty's Day!

Mark Cronk

=COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell<DATE(2021, 3, 31)))

Mark

Lynda Ross

OMG! Fabulous!

Yes, it sure answered my question!

Thankd you so much! I'm an avid learner!

Thank you!

Mark Cronk

Glad you found a solution. Happy to help. You had a couple syntax errors. I added the ISDATE to avoid getting an error.

Thanks for contributing to the Community.

Mark

Lynda Ross

@Mark Cronk ,

So now to add to your corrected formula, what I now need is: Tasks with the "Complete" Status that fall between two dates. Working from your wonderful formula I tried this but it comes back unparseable.

=COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell>DATE(2021, 3, 31), AND(ISDATE (@cell), @cell<DATE(2021, 2021, 5, 2))))

Is the thinking wrong that I can have two AND functions - one with a < and another

>FUNCTION?

Thanks again!

Mark Cronk

Try this:

=COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell>DATE(2021, 3, 31), @cell<DATE(2021, 5, 2)))

Mark

Lynda Ross

@Mark Cronk - again, fabulous! Thank you so much! I haven't worked with many AND & ISDATE functions so I appreciate your help!

Happy St. Patty's Day!

Mark Cronk

Happy to help any time.

Mark