ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   date functions (https://www.excelbanter.com/excel-discussion-misc-queries/62954-date-functions.html)

TUNGANA KURMA RAJU
date functions
 
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
date functions
 
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?

TUNGANA KURMA RAJU
date functions
 
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?

Biff
date functions
 
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?

TUNGANA KURMA RAJU
date functions
 
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?

Biff
date functions
 
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?

TUNGANA KURMA RAJU
date functions
 
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?


All times are GMT +1. The time now is 08:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com