Function to Return a Date Value Q

[Seanie]

I looking for a function that will return the value that is in one of
5 cells that is matches the closest to Todays date. Thus -

I have 5 date values in cells A5 to E5
Todays date is in A1
In A10 I want to return the value of one of A5-E5 that is closest to
todays date that is in A1

 

[joeu2004]

I looking for a function that will return the value
that is in one of 5 cells that is matches the closest
to Todays date. Thus -
I have 5 date values in cells A5 to E5
Todays date is in A1
In A10 I want to return the value of one of A5-E5 that
is closest to todays date that is in A1

Depends on what you mean by "closest". Perhaps:

=LOOKUP(A1,A5:E5)

formatted as Date. But that returns the closest date that is the same or
earlier. So if B5 contains 3/1/2012 (March 1), C5 contains 4/1/2012 and A1
contains 3/31/2012, LOOKUP will return 3/1/2012, not 4/1/2012. Okay?

Note: LOOKUP requires that A5:E5 be in ascending order. Okay?

 

[seanryanie]

Depends on what you mean by "closest". Perhaps:

=LOOKUP(A1,A5:E5)

formatted as Date. But that returns the closest date that is the same or
earlier. So if B5 contains 3/1/2012 (March 1), C5 contains 4/1/2012 and A1
contains 3/31/2012, LOOKUP will return 3/1/2012, not 4/1/2012. Okay?

Note: LOOKUP requires that A5:E5 be in ascending order. Okay?

Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date

 

[Claus Busch]

Hi Sean,

Am Mon, 25 Jun 2012 10:55:06 -0700 (PDT) schrieb (e-mail address removed):

Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date

try:
=MIN(IF(ABS(A5:E5-$A$1)=MIN(ABS(A5:E5-$A$1)),A5:E5))
and enter the formula with CTRL+Shift+Enter


Regards
Claus Busch

 

[seanryanie]

Hi Sean,

Am Mon, 25 Jun 2012 10:55:06 -0700 (PDT) schrieb (e-mail address removed):


try:
=MIN(IF(ABS(A5:E5-$A$1)=MIN(ABS(A5:E5-$A$1)),A5:E5))
and enter the formula with CTRL+Shift+Enter


Regards
Claus Busch

Bingo! that seems to have worked great, Thanks