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

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

--
Biff
Microsoft Excel MVP


"jem264" wrote in message
...
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

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 DATE

--
Biff
Microsoft Excel MVP


"jem264" wrote in message
...

Previous Posts In This Thread:

On Monday, January 14, 2008 11:41 AM
jem26 wrote:

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

On Monday, January 14, 2008 1:33 PM
T. Valko wrote:

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 DATE

--
Biff
Microsoft Excel MVP


"jem264" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Custom Validation Using the Enterprise Library
http://www.eggheadcafe.com/tutorials...ation-usi.aspx

If you want the minimum date that is greater than today...

Array entered** :

=MIN(IF(A1:A10TODAY(),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

--
Biff
Microsoft Excel MVP


Linda Racine wrote in message
...
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 DATE

--
Biff
Microsoft Excel MVP


"jem264" wrote in message
...

Previous Posts In This Thread:

On Monday, January 14, 2008 11:41 AM
jem26 wrote:

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

On Monday, January 14, 2008 1:33 PM
T. Valko wrote:

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 DATE

--
Biff
Microsoft Excel MVP


"jem264" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Custom Validation Using the Enterprise Library
http://www.eggheadcafe.com/tutorials...ation-usi.aspx