look-up a date in a range and find the date that is closest todays date

**VanShark** · 03-12-2014, 10:46 AM

I would like to look-up a date in a range and find the date that is closest today's date and return that value to a cell (C2):

B2: Todays Date: 1/30/14
C2: Next Upcoming date ?

A1: 2/1/14
A2: 2/2/14
A3: 3/1/14

As an added challenge, if there is date in the range that is older than today’s date (past due), I would like it to return that value:

B2: Todays Date: 1/30/14
C2: Next Upcoming date ?

A1: 2/1/14
A2: 2/2/14
A3: 3/1/14
A4: 1/1/14

**Tony Valko** · 03-12-2014, 10:56 AM

If I understand, maybe this array formula**:

=IF(COUNTIF(A1:A4,"<="&TODAY()),MIN(IF(A1:A4<=TODAY(),A1:A4)),MIN(IF(A1:A4>=TODAY(),A1:A4)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**VanShark** · 03-12-2014, 12:11 PM

Very Helpful... Thank You!!

**Tony Valko** · 03-12-2014, 09:46 PM

You're welcome. Thanks for the feedback!

look-up a date in a range and find the date that is closest todays date

LinkBack

Thread Tools

Rate This Thread

Display

look-up a date in a range and find the date that is closest todays date

Re: look-up a date in a range and find the date that is closest todays date

Re: look-up a date in a range and find the date that is closest todays date

Re: look-up a date in a range and find the date that is closest todays date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Function/formula to compare and insert todays date or yesterdays date

[SOLVED] Excel 2007 : How to find the latest date in a range of dates, that is before todays date

Given an end date find corresponding start date 3 months prior ..quarter date range

find closest date (number) in a range

Using VB to find a range based on todays date and todays date +30

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions