Forum Discussion

SteveBoat's avatar
SteveBoat
Copper Contributor
Jul 19, 2021
Solved

Find the next date greater than today from a range

I have a range of cells with dates. Some dates are older than today, some are today and several are greater than today.

I want to find the next date that is greater than today. E.g.

Today is 7/15

The range has 7/10, 7/12, 7/15, 7/18, 7/19, 7/22, 7/25.

I want a formula that finds 7/18 (the next date greater than today).

Any help will be appreciated.

excel

10 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jul 19, 2021

    SteveBoat 

     

    Another approach is to take advantage of the recent FILTER function.

     

    Assuming dates are in A1:A10,

    =MIN(

    FILTER(A1:A10,A1:A10>today())

    )

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 19, 2021

    SteveBoat 

    Let's say the range with dates is A2:A32.

    If you have Excel 2019 or Excel in Microsoft 365:

    =MINIFS(A2:A32,A2:A32,">"&TODAY())

    For earlier versions, use the following array formula confirmed with Ctrl+Shift+Enter:

    =MIN(IF(A2:A32>TODAY(),A2:A32))

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jul 19, 2021

    SteveBoat 

    Example in the attached file

    =MIN(IF(A1:A10>D1,A1:A10))

    Contains array formula: Do not enter the border {}, complete the formula with CTRL + SHIFT + RETURN!

     

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

    • SteveBoat's avatar
      SteveBoat
      Copper Contributor
      Jul 19, 2021
      I'm looking for something that doesn't need the CNTL, SHIFT, RETURN.
      Thanks though

Resources