Qualifying Date Range
I have a list of contracts and need to know:
Which are coming due in 30 days
in 60 days
in 90 days
But when I use <=TODAY(+30), it's returning everything over 30 days not just those 30 days out. How do I ask it to only give me those that are 30 days out and not count the ones that are 31+ days?
and then, how do I ask it to return those that are 31 - 60 days; 61 - 90 days?
My current formula is: =COUNTIFS(Completed:Completed, 0, [Completion Date]:[Completion Date], <=TODAY(+30))
I want it to check the completed column first and if there is no flag to check the Completion Date column and return any with the criteria that I've mentioned above.
Best Answer
-
You would need to add in some criteria for the other end of the range.
=COUNTIFS(Completed:Completed, 0, [Completion Date]:[Completion Date], AND(@cell >= TODAY(), @cell <=TODAY(+30)))
Answers
-
You would need to add in some criteria for the other end of the range.
=COUNTIFS(Completed:Completed, 0, [Completion Date]:[Completion Date], AND(@cell >= TODAY(), @cell <=TODAY(+30)))
-
Got it!!!
-
Great! Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!