About Us

Thank you,I have mistaken.I didn't used CNTR+SHIFT+ENTER

"Biff" wrote:

Take a look at this sample file:

http://s40.yousendit.com/d.aspx?id=0...90YDLHVMB3AJIS

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
Thank you,Biff ,but both functions have not given desired results.I have
used
for question 1,intermediate cell method nad for second Question c1=
today()
method .
1 st question I got as 01-DEC-2005 result which is incorrect. 2 nd
question
I got N/A error.
"Biff" wrote:

Hi!

To extract dates that are within the range (inclusive):

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(SUMPRODUCT(--(B$1:B$6=A$1),--(B$1:B$6<=A$2))=ROWS($1:1),INDEX(B$1:B$6,SMALL(IF ((B$1:B$6=A$1)*(B$1:B$6<=A$2),ROW(B$1:B$6)),ROWS( $1:1))),"")

Copy down until you get blanks. Format the cell(s) as DATE.

Notes:

You can make the formula shorter (less complicated?) by using an
intermediate cell to count the number of instances that meet the date
criteria: Use one of these in, say, D1:

=COUNTIF(B1:B6,"="&A1)-COUNTIF(B1:B6,""&A2)

=SUMPRODUCT(--(B1:B6=A1),--(B1:B6<=A2))

Then just refer to that cell:

=IF(D$1=ROWS($1:1),INDEX(B$1:B$6,SMALL(IF((B$1:B$ 6=A$1)*(B$1:B$6<=A$2),ROW(B$1:B$6)),ROWS($1:1))), "")

To extract the closest date to today:

Also entered as an array:

=INDEX(B1:B6,MATCH(MIN(ABS(B1:B6-TODAY())),ABS(B1:B6-TODAY()),0))

Format the cell as DATE

Note:

Another way to do this is to use a cell to hold today's date:

C1 = =TODAY()

Then just refer to that cell:

=INDEX(B1:B6,MATCH(MIN(ABS(B1:B6-C1)),ABS(B1:B6-C1),0))

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
Yes,I want to extract the values to other cells,for both questions?
For second question the function must test the used range(B1:B3)(actual
range is B1:B6,B4:B6 is blank) and from that range nearest date to
taday's
date is 19-DEC-2005,that has to be extracted.

"Biff" wrote:

Hi!

What functions will fetch me the desired results?

Depends on how you want to do it. Do you want to extract the values to
other
cells or maybe you just want to highlight the values with conditional
formatting?

2.What date in range B1:B3 is nearest to today?
ans: 19-DEC-2005

Yeah, based on your limited example. What about this:

Today is 1/4/2006

Which date is closer?

12/19/2005
1/20/2006

Biff

"TUNGANA KURMA RAJU"
wrote
in
message ...
I have two questions:
col A -----------Col B
05-DEC-2005------01-DEC-2005
20-DEC-2005------02-DEC-2005
------19-DEC-2005

1.Which date or dates in range B1:B3 falls in range A1:A2(including
both
dates) ?
or (Is any date/s in B1:B3 falls between A1 and A2?)
ans: 19-DEC-2005
2.What date in range B1:B3 is nearest to today?
ans: 19-DEC-2005
What functions will fetch me the desired results?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Advanced Date Functions	enright_m	Excel Worksheet Functions	1	October 26th 05 04:08 PM
Date functions	John	New Users to Excel	3	August 15th 05 08:50 PM
Date difference	Maxi	Excel Worksheet Functions	7	July 28th 05 01:38 PM
Date Math Problem	Dkline	Excel Worksheet Functions	4	March 4th 05 04:11 PM
date and time	ladimples247	New Users to Excel	2	February 16th 05 08:52 PM

Menu

About Us