Finding Nearest and Furthest Date

[carl]

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.

 

[Bob Phillips]

=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:B20>TODAY(),B2:B20))

--

 

[Ron Rosenfeld]

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

 

[carl]

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]

=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)