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 ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 19, 2021

SteveBoat 

As variant

=XLOOKUP(TODAY()+0.1,Dates,Dates,,1)
  • SteveBoat's avatar
    SteveBoat
    Copper Contributor
    Jul 19, 2021
    Not sure how to referece the range. When I put in A1:A20 I get a #NAME? result.
      • SteveBoat's avatar
        SteveBoat
        Copper Contributor
        Jul 19, 2021
        Perfect! The AGGREGATE function seems to do it well.

        Thank you very much.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 19, 2021

      SteveBoat 

      You may use direct reference

      =XLOOKUP(TODAY()+0.1,A1:A20,A1:A20,,1)

      assuming XLOOKUP is available in your version of Excel.

Resources