Finding Nearest and Furthest Date
 

My data table is like so. My date format is YY/M

Today 7-Aug

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan

Trying to create this table. Basically I am trying to find the date in the
data table that is the closest and furthest away from today's date.

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

Thank you in advance.

Finding Nearest and Furthest Date
 

=MIN(IF(B2:B20<TODAY(),B2:B20))

HTHwhich is an array formula, it should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

and similar for MAX

=MAX(IF(B2:B20TODAY(),B2:B20))

--
---

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
My data table is like so. My date format is YY/M

Today 7-Aug

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan

Trying to create this table. Basically I am trying to find the date in the
data table that is the closest and furthest away from today's date.

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

Thank you in advance.

Finding Nearest and Furthest Date
 

On Mon, 20 Aug 2007 09:30:05 -0700, carl
wrote:

My data table is like so. My date format is YY/M

Today 7-Aug

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan

Trying to create this table. Basically I am trying to find the date in the
data table that is the closest and furthest away from today's date.

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

Thank you in advance.




You could use a Pivot Table.

Below is the result I got with your data

Stock DateMax DateMin
OIH 9-Jan 7-Aug
SPY 10-Dec 7-Oct

I dragged Stock to the Rows Area
I dragged Date to the Data area, and changed the field setting to Min of Dates,
renaming DateMin
I dragged Date to the Data area (again), and changed the field setting to Max
of Dates, renaming DateMax

I then selected a report format that was similar to what you used above.




--ron

Finding Nearest and Furthest Date
 

Thanks Again Bob.

I did not explain my problem very well.

The new table

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

For the formula in B2. I need it to look at A2 and find the cooresponding
Max or Min Date in the data table. I tried your formula but could not get it
to return the correct values.

Here's the data table again:

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan


Thanks Again.



"Bob Phillips" wrote:

=MIN(IF(B2:B20<TODAY(),B2:B20))

HTHwhich is an array formula, it should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

and similar for MAX

=MAX(IF(B2:B20TODAY(),B2:B20))

--
---

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
My data table is like so. My date format is YY/M

Today 7-Aug

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan

Trying to create this table. Basically I am trying to find the date in the
data table that is the closest and furthest away from today's date.

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

Thank you in advance.

Finding Nearest and Furthest Date
 

=MIN(IF((A2:A20="SPY")*(B2:B20<TODAY()),B2:B20))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
Thanks Again Bob.

I did not explain my problem very well.

The new table

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

For the formula in B2. I need it to look at A2 and find the cooresponding
Max or Min Date in the data table. I tried your formula but could not get
it
to return the correct values.

Here's the data table again:

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan


Thanks Again.



"Bob Phillips" wrote:

=MIN(IF(B2:B20<TODAY(),B2:B20))

HTHwhich is an array formula, it should be committed with
Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

and similar for MAX

=MAX(IF(B2:B20TODAY(),B2:B20))

--
---

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"carl" wrote in message
...
My data table is like so. My date format is YY/M

Today 7-Aug

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan

Trying to create this table. Basically I am trying to find the date in
the
data table that is the closest and furthest away from today's date.

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

Thank you in advance.