Formula to exclude expired years

KayH

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.

Hollie Green

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.

Hollie Green

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.

KayH

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?

Hollie Green

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())

KayH

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