+ Reply to Thread
Results 1 to 5 of 5

Find closest date to today in a row of dates

  1. #1
    Registered User
    Join Date
    07-01-2014
    Location
    Trowbridge, England
    MS-Off Ver
    2007
    Posts
    22

    Find closest date to today in a row of dates

    Hi all,

    I have a bunch of key dates (business reviews) that span a year or so that are held in a row (AC:CN). In a summary page I would like to return the nearest date to todays date. This way the team will always know what date the next review is. (I cannot use a calendar for this!!)

    I hope you can help!

    Thanks
    Scott

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Find closest date to today in a row of dates

    The first comming (next) day or (earlier) day?

    I think you need the next day.

    It would be helpfull (for me) if you add an small exel file, without confidential information.

    Please add also the desired (expected) result.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Find closest date to today in a row of dates

    I use something like this that may convert to what you want

    Please Login or Register  to view this content.
    So column D:D would be what you want to return and column A:A would be where you are searching...

    HTH
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Find closest date to today in a row of dates

    Assuming that the dates arranged in increasing order;
    Assuming that the dates are in one row, AC1:CN1 for example:

    =INDEX(AC1:CN1, MATCH( TODAY(), AC1:CN1, 1) +1)

    Note that if there is a review "today" it will display the review after today.

  5. #5
    Registered User
    Join Date
    07-01-2014
    Location
    Trowbridge, England
    MS-Off Ver
    2007
    Posts
    22

    Re: Find closest date to today in a row of dates

    Thank you so much (all of you that responded). Thanks Ben_hensel - perfect!

    Quote Originally Posted by ben_hensel View Post
    Assuming that the dates arranged in increasing order;
    Assuming that the dates are in one row, AC1:CN1 for example:

    =INDEX(AC1:CN1, MATCH( TODAY(), AC1:CN1, 1) +1)

    Note that if there is a review "today" it will display the review after today.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to find date closest to today
    By mattress58 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2014, 12:18 PM
  2. [SOLVED] look-up a date in a range and find the date that is closest todays date
    By VanShark in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2014, 09:46 PM
  3. [SOLVED] Find Entry With Closest Date and Time to Today
    By Steve N. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2012, 06:09 PM
  4. Vlookup does not find closest match (dates)
    By Stefan.hagnesten in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2009, 10:38 AM
  5. Finding the closest future date from Today from a list of dates
    By maharg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2008, 09:45 AM

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