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

Michelle Valdes

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

KPH

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

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

KPH

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

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

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

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

Michelle Valdes

KPH

Great news! Thanks for letting me know.