date functions

[Guest]

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?

 

[Biff]

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

 

[Guest]

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]

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

 

[Guest]

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]

Take a look at this sample file:

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

Biff

 

[Guest]

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