Date Range in formula unparseable

Options
Sandra Unger
Sandra Unger ✭✭✭
edited 09/23/21 in Formulas and Functions

Hi I would be very appreciative if someone could help me solve this date range formula.

=COUNTIFS(Status:Status, "Complete", [IS&T Services]:[IS&T Services], "<>Public Safety", [IS&T Services]:[IS&T Services], "<>Cross Functional", [IS&T Services]:[IS&T Services], "<>SubProjects and Tasks", [Actual Finish]:[Actual Finish], >=DATE(2020, 7, 1), [Actual Finish]:[Actual Finish], <=DATE(2021, 6, 30)))

I want to count the number of completed projects between July 1, 2020 and June 30, 2021.

Thank you!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Your date range portions are correct. It is the Services portions that are causing an issue. You need to move the quotes to around only the text and not include the <> in the quotes.

  • Sandra Unger
    Options

    Hi Paul, thanks for the suggestion but it didn't work - still unparseable. However, I tried removing one of the parenthesis at the end of the formula and it is now returning results. Does that make sense?

    =COUNTIFS(Status:Status, "Complete", [IS&T Services]:[IS&T Services], "<>Public Safety", [IS&T Services]:[IS&T Services], "<>Cross Functional", [IS&T Services]:[IS&T Services], "<>SubProjects and Tasks", [Actual Finish]:[Actual Finish], <=DATE(2021, 6, 30), [Actual Finish]:[Actual Finish], >=DATE(2020, 7, 1))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It does make sense. Are each of your entries in the [IS&T Services] prefixed by <>?


    <> is also the operator for "not equal to" (opposite to "=" ). That's why I initially thought that was the issue.


    But yes. Too many closing parenthesis on the end of the formula can break it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!