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?
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" <[email protected]> wrote in
message news:[email protected]...
>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?
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" <[email protected]> wrote in
> message news:[email protected]...
> >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?
>
>
>
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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >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?
>>
>>
>>
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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >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?
> >>
> >>
> >>
>
>
>
Take a look at this sample file:
http://s40.yousendit.com/d.aspx?id=0...90YDLHVMB3AJIS
Biff
"TUNGANA KURMA RAJU" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> > 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" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >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?
>> >>
>> >>
>> >>
>>
>>
>>
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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> > 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" <[email protected]>
> >> >> wrote
> >> >> in
> >> >> message news:[email protected]...
> >> >> >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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks