Finding Max/Min dates from a table

[ramon75]

Hello,

I hope someone can help me.
How can I get the max/min dates from a table based on a person's data.
For instance, I have the table as in the table below, where I am trying to get each person max and min date that they got paid.
In red on the right are values that I typed based on the table for Person 1.
But how to make a formula for this please?
Thank you in advance.

[TABLE="width: 1099"]
<tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Earnings[/TD]
[TD="align: right"]01-Jan-16[/TD]
[TD="align: right"]02-Jan-16[/TD]
[TD="align: right"]03-Jan-16[/TD]
[TD="align: right"]04-Jan-16[/TD]
[TD="align: right"]05-Jan-16[/TD]
[TD="align: right"]06-Jan-16[/TD]
[TD="align: right"]07-Jan-16[/TD]
[TD="align: right"]08-Jan-16[/TD]
[TD="align: right"]09-Jan-16[/TD]
[TD="align: right"]10-Jan-16[/TD]
[TD="align: right"]11-Jan-16[/TD]
[TD="align: right"]12-Jan-16[/TD]
[TD="align: right"]13-Jan-16[/TD]
[TD="align: right"]14-Jan-16[/TD]
[TD="align: right"]15-Jan-16[/TD]
[TD] [/TD]
[TD]Minimum Date[/TD]
[TD]Maximum Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peron 1[/TD]
[TD="align: right"]$500[/TD]
[TD="align: right"]$550[/TD]
[TD="align: right"]$578[/TD]
[TD="align: right"]$606[/TD]
[TD="align: right"]$637[/TD]
[TD="align: right"]$669[/TD]
[TD="align: right"]$702[/TD]
[TD="align: right"]$737[/TD]
[TD="align: right"]$774[/TD]
[TD="align: right"]$813[/TD]
[TD="align: right"]$853[/TD]
[TD="align: right"]$896[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]01-Jan-16[/TD]
[TD="align: right"]12-Jan-16[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Peron 2[/TD]
[TD] [/TD]
[TD="align: right"]$300[/TD]
[TD="align: right"]$315[/TD]
[TD="align: right"]$331[/TD]
[TD="align: right"]$347[/TD]
[TD="align: right"]$365[/TD]
[TD="align: right"]$383[/TD]
[TD="align: right"]$402[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Peron 3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$210[/TD]
[TD="align: right"]$221[/TD]
[TD="align: right"]$232[/TD]
[TD="align: right"]$243[/TD]
[TD="align: right"]$255[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Peron 4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$200[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Peron 5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$500[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$500[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Peron 6[/TD]
[TD="align: right"]$300[/TD]
[TD] [/TD]
[TD="align: right"]$400[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$600[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$400[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Peron 7[/TD]
[TD] [/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$200[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Peron 8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$700[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Peron 9[/TD]
[TD] [/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Peron 10[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"]$150[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="15"><col><col><col></colgroup>[/TABLE]

 

[tygrrboi]

for Min

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}</style>=INDEX($B$1:$P$1, MATCH(AGGREGATE(5,3,$B2:$P2), $B2:$P2,0))

for Max

=INDEX($B$1:$P$1, MATCH(AGGREGATE(4,3,$B2:$P2), $B2:$P2,0))


(only will work with Excel 2010 or higher versions)

 

[Tetra201]

Cell R2 =MIN(IF($C2:$Q2 < > "",$C$1:$Q$1))
Cell S2 =MAX(IF($C2:$Q2 < > "",$C$1:$Q$1))
Both formulas should be entered with Ctrl+Shift+Enter

 

[igold]

Cell R2 =INDEX($B$1:$P$1,MATCH(MIN($B2:$P2),$B2:$P2,0))
Cell S2 =INDEX($B$1:$P$1,MATCH(MAX($B2:$P2),$B2:$P2,0))

Both formulas copied down.

igold

 

[RedBeard]

Never mind, see the above, hah.

 

[Maverick23]

How can I find the next minimum date from a table? I have a table of dates that are arranged out of sequence and many are the same date. I want to find the minimum date and in next cell, the next minimum date, and so on until all dates are arranged. I tried ranking the dates in ascending order but ranking skips numbers based on the number of same dates.