Formula - Completed projects in a given year

Mallory McFallMallory McFall ✭✭✭✭✭
edited 01/29/21 in Formulas and Functions
01/29/21 Edited 01/29/21
Answered - Pending Review

Hello,

The formula pasted below works great if I want to count the number of projects completed in a year. How can I revise the formula so that I can count the number of projects completed in 2020?


Thanks!


Popular Tags:

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Just change YEAR(TODAY()) to 2020.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Mallory McFall

    Try something like this.

    IFERROR(YEAR(@cell); 0) = 2020

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Mallory McFallMallory McFall ✭✭✭✭✭

    @Paul Newcome Its returning an Invalid Operation message


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You will want to remove the YEAR function completely.

  • Mallory McFallMallory McFall ✭✭✭✭✭

    @Paul Newcome When I did that, it returned the value 0 - the correct value should be 6.

  • Mallory McFallMallory McFall ✭✭✭✭✭

    @Paul Newcome I ended up using the following function to calculate the answer: {Completion Date}, CONTAINS([email protected], @cell))


    Thank you for your assistance !

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️


    If you completion date range was referencing an actual date, the CONTAINS function will work, but so will Andree's solution of

    IFERROR(YEAR(@cell), 0) = 2020


    Based on the criteria in your originally provided formula, it appeared as if the data in the completion date range was just the year.

Sign In or Register to comment.