Max Function find second newest date

[mrpudley]

Hello all,

I have found how to find the second newest date using the Large function. However it only works when there is only one instance of the date. I have a table with were each date is recorded three times for three different products. When I use =Large(A:A, 2) it returns the max date as the bottom three rows of the table have that.

My table looks something like this:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]8/28/2016[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD]8/28/2016[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]8/28/2016[/TD]
[TD]Product 3[/TD]
[/TR]
[TR]
[TD]9/4/2016[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD]9/4/2016[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]9/4/2016[/TD]
[TD]Product 3[/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts or suggestions are appreciated.

Thanks,

Brent

 

[sheetspread]

Something like:

Excel 2010
	A	B
1	Week	Product
2	8/28/2016	Product 1
3	8/28/2016	Product 2
4	8/28/2016	Product 3
5	9/4/2016	Product 1
6	9/4/2016	Product 2
7	9/4/2016	Product 3
8
9		8/28/2016
Sheet7

Cell Formulas
Range	Formula
B9	{=LARGE(IF(FREQUENCY($A$2:$A$7,$A$2:$A$7)>0,$A$2:$A$7),2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrpudley]

Something like:

Excel 2010

	A	B
Week	Product
Product 1
Product 2
Product 3
Product 1
Product 2
Product 3

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8/28/2016[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8/28/2016[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8/28/2016[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]9/4/2016[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]9/4/2016[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]9/4/2016[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/28/2016[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=LARGE(IF(FREQUENCY($A$2:$A$7,$A$2:$A$7)>0,$A$2:$A$7),2)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks however it returns #NUM! I tried using both the range A2:A7 and =DataTable[Week] but neither worked. (DataTable is the name of my table).

Thanks for you time, it is appreciated.

 

[sheetspread]

Here's a table example:

Excel 2010
	A	B
1	Week	Product
2	8/28/2016	Product 1
3	8/28/2016	Product 2
4	8/28/2016	Product 3
5	9/4/2016	Product 1
6	9/4/2016	Product 2
7	9/4/2016	Product 3
8
9		8/28/2016
Sheet7

Cell Formulas
Range	Formula
B9	{=LARGE(IF(FREQUENCY(Table1,Table1)>0,Table1),2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrpudley]

Thanks! That worked perfectly.

Cheers,

Brent