Formula to exclude expired years

Options

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.

Tags:

Best Answer

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓
    Options


    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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓
    Options


    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
    KayH
    edited 01/03/23
    Options

    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
    Hollie Green ✭✭✭✭✭✭
    edited 01/04/23
    Options

    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
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!