+ Reply to Thread
Results 1 to 5 of 5

Next Date in Range

  1. #1
    Registered User
    Join Date
    09-26-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Next Date in Range

    Hi -
    I am looking for a formula that will reference a cell with today's date and then find the next date in a column with approximately 40 quarterly or semi-annual dates.

    Thanks for your help.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Next Date in Range

    Hi, your dates in A2:A100

    =index(a2:a100,match(today(),a3:a101,1))

    or

    =index(a2:a100,match(today(),a2:a100,1)+1)


    Regards
    Last edited by canapone; 09-26-2011 at 01:50 AM. Reason: 0 to 1

  3. #3
    Registered User
    Join Date
    09-26-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Next Date in Range

    Thanks for this. I tried this formula and got the #n/a error. I think the issue is that today's date is not in the range of dates - what I am looking for is the next date after today's date in the column. Example - if today's date is Sept 26, 2011 and the array has quarterly dates from Sept 30, 2010 to Sept 30, 2020, I am looking for a formula that would pull the next date in the range after today`s date (Sept 30, 2011).

    Thanks

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Next Date in Range

    Hi,

    waiting for better solutions

    =IF(ISNA(INDEX(A2:A20,MATCH(C2,A2:A20,0))),INDEX(A2:A20,MATCH(C2,A2:A20,1)+1),INDEX(A2:A20,MATCH(C2,A2:A20,0))


    Regards
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-26-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Next Date in Range

    That works great, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1