Trying to get # of Projects Active Longer Than 12 Months for 2023
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
-
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.
-
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.
-
Answers
-
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.
-
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.
-
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.
-
That did it @KPH ! I really appreciate your help with this!
-
-
Great news! Thanks for letting me know.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!