Trying to get # of Projects Active Longer Than 12 Months for 2023

Options
Michelle Valdes
Michelle Valdes ✭✭✭
edited 04/05/24 in Formulas and Functions

I am trying to get the # of projects active longer than 12 months for 2023. I created a report which shows 12 projects but my formula is coming back with 41 projects and I can't figure out what I am doing wrong.

Report Filters:


Formula on Metric Sheet for 2023:

Thanks in advance,

Michelle

Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Michelle Valdes

    I think the problem is with the end date. The report filter is including rows where the end date is on or after 1/1/23 and on or before 9/1/23, or the end date is blank.

    The formula is including rows where the end date is on or after 1/1/23, or on or before 9/1/23, or the end date is blank.

    So any end date would be included in the formula count because any date will either be after 1/1/23 or before 9/1/23. Only those between these two dates will be returned by the report filter.

    I also noticed that your formula has 2 COUNTIFS. I assume the report also has two sheets, Status is from one and Status 2 is from the other. Is not, there may be another issue there.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    You can replace the OR with and AND like you have done for Start Date.


    AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 9, 1))

    will return only the rows with dates between 1/2/23 and 1/9/23.


    To also include blank end dates you can combine this with an OR, like this:

    OR(@cell = "", AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 9, 1)))


    You'll need to change this in both of the COUNTIFS that you have.

    If you struggle, please paste your original formula here and I will edit it.

  • Michelle Valdes
    Michelle Valdes ✭✭✭
    Answer ✓
    Options

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Michelle Valdes

    I think the problem is with the end date. The report filter is including rows where the end date is on or after 1/1/23 and on or before 9/1/23, or the end date is blank.

    The formula is including rows where the end date is on or after 1/1/23, or on or before 9/1/23, or the end date is blank.

    So any end date would be included in the formula count because any date will either be after 1/1/23 or before 9/1/23. Only those between these two dates will be returned by the report filter.

    I also noticed that your formula has 2 COUNTIFS. I assume the report also has two sheets, Status is from one and Status 2 is from the other. Is not, there may be another issue there.

  • Michelle Valdes
    Options

    Hello @KPH - thank you for your reply. Makes sense. What do you recommend I do differently with my formula? Also, you are correct. I am referencing two different sheets.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    You can replace the OR with and AND like you have done for Start Date.


    AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 9, 1))

    will return only the rows with dates between 1/2/23 and 1/9/23.


    To also include blank end dates you can combine this with an OR, like this:

    OR(@cell = "", AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 9, 1)))


    You'll need to change this in both of the COUNTIFS that you have.

    If you struggle, please paste your original formula here and I will edit it.

  • Michelle Valdes
    Options

    That did it @KPH ! I really appreciate your help with this!

  • Michelle Valdes
    Michelle Valdes ✭✭✭
    Answer ✓
    Options
  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Great news! Thanks for letting me know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!