Formula to find the date closest to today.

  • Thread starter Thread starter jem264
  • Start date Start date
J

jem264

I have a list of dates in row 1, from B1:BK1. Most of them are Sundays, but a
few are in the middle of the week. I need a formula that I can enter into
another sheet to find the date from this list that is closest to today(). I
found an array formula that will work, I hope to be able to do this without
the array, I do not want to add any more data to the sheets, if possible.
Thanks in advance
 
find the date from this list that is closest to today().

I assume that closest means either before or after (if not an exact match).

Normally entered:

=INDEX(B1:BK1,MATCH(TRUE,INDEX(ABS(B1:BK1-TODAY())=MIN(INDEX(ABS(B1:BK1-TODAY()),1,)),1,),0))

Format as DATE
 
How does the formula need to change if one only wants to return a date after today?



T. Valko wrote:

I assume that closest means either before or after (if not an exact match).
14-Jan-08

I assume that closest means either before or after (if not an exact match)

Normally entered

=INDEX(B1:BK1,MATCH(TRUE,INDEX(ABS(B1:BK1-TODAY())=MIN(INDEX(ABS(B1:BK1-TODAY()),1,)),1,),0)

Format as DAT

--
Bif
Microsoft Excel MV


Previous Posts In This Thread:

Formula to find the date closest to today.
I have a list of dates in row 1, from B1:BK1. Most of them are Sundays, but a
few are in the middle of the week. I need a formula that I can enter into
another sheet to find the date from this list that is closest to today(). I
found an array formula that will work, I hope to be able to do this without
the array, I do not want to add any more data to the sheets, if possible.
Thanks in advance

I assume that closest means either before or after (if not an exact match).
I assume that closest means either before or after (if not an exact match)

Normally entered

=INDEX(B1:BK1,MATCH(TRUE,INDEX(ABS(B1:BK1-TODAY())=MIN(INDEX(ABS(B1:BK1-TODAY()),1,)),1,),0)

Format as DAT

--
Bif
Microsoft Excel MV



Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Custom Validation Using the Enterprise Library
http://www.eggheadcafe.com/tutorial...c-33ef1ec7d1a3/wpf-custom-validation-usi.aspx
 
If you want the minimum date that is greater than today...

Array entered** :

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

** 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.

Format as Date
 
Back
Top