Formula to exclude expired years
Hi Hi,
I'd like to count how many "current" contracts we have and also how many we have up coming close to expiry (say in the next 60 days)
My reference sheet has a column for estimated end date, I just can't find the right formula to exclude dates that fall prior to the current date.
And I have no idea how to write the formula to look for future dates... I've been looking on the discussion boards but can't find anything that seems relevant.
Best Answer
-
The below formula will count all dates in the date column that is between today and 60 days from today
=COUNTIFS(Date:Date, >=TODAY(), Date:Date, <=TODAY(60)). You would replace the Date:Date with the title of your column with the date in it.
Answers
-
The below formula will count all dates in the date column that is between today and 60 days from today
=COUNTIFS(Date:Date, >=TODAY(), Date:Date, <=TODAY(60)). You would replace the Date:Date with the title of your column with the date in it.
-
Thanks heaps Hollie, That is great and works perfectly (after I noticed my error!)
For a separate formula - Do you know how I'd count current contracts? so exclude any past todays current date?
-
If I am understanding correctly you want to only count contracts with today's date if that is the case you would do
=Countif(Date:Date,=Today())
If you want to count any contract prior to or equal to today it would be
=Countif(Date:Date,<=Today())
-
Hi Hollie,
Thanks so much, that makes sense.
I've adjusted it slightly, as I need to count currently running trials which means I'm counting any contract with an end date from today or after (ie: date not in the past, as they have ended already)
=COUNTIF({Clinical Trials : Accepted Range 1}, >=TODAY()
Have I interpreted that correctly?
Sorry if it doesn't make sense... it's so hard to describe
Kay
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!