Dates and SMALL Formula

[Fin Fang Foom]

Hi everyone,


I have dates like this in column A:

1060911
1060914
1060912
1060915
1060913
and so on...

I would like to get the smallest date and then the second and third smallest dates in column E.

It should look like this:

09/11/2006
09/12/2006
09/13/2006

I have this formula, it will get me the Min of the date.

=DATE(INT(MIN($A$2:$A$851/10000)),MID(MIN($A$2:$A$851),4,2),RIGHT(MIN($A$2:$A$851),2))

I tried to incorporate the small function into the formula to get the smallest dates but it does not work.

=DATE(INT(SMALL($A$2:$A$851,ROW(A1))/10000),MID(SMALL(Sheet2!$A$2:$A$851,ROW(A1)),4,2),RIGHT(SMALL($A$2:$A$851,ROW(A1)),2))


If possible I would like a all in one formula.

Book3
	A	B	C	D	E	F
1	DATE		MIN
2	1060912		9/11/2006		#NUM!
3	1060912				#NUM!
4	1060912				#NUM!
5	1060914				#NUM!
6	1060911				#NUM!
7	1060912				#NUM!
8	1060914
9	1060911
10	1060911
11	1060911
12	1060913
13	1060911
14	1060911
15	1060911
16	1060911
17	1060915
18	1060911
Sheet1

 

[Fin Fang Foom]

Hi guys,


I would like to add one more function into the formula but I can't get it to work. I would like to add the INDIRECT function. I got this so far:

=TEXT(MIN(IF(INDIRECT("A2:A4000")<>"",IF(ISNUMBER(MATCH(DATE(2000+(100*LEFT($A$2:$A$4000)-100)+MID($A$2:$A$4000,2,2),MID($A$2:$A$4000,4,2),MID($A$2:$A$4000,6,2)),J$1:J1+0,0)),"",DATE(2000+(100*LEFT($A$2:$A$4000)-100)+MID($A$2:$A$4000,2,2),MID($A$2:$A$4000,4,2),MID($A$2:$A$4000,6,2))))),"m-dd-yyyy")


But it wont allow me to add the indirect to the other ranges in the formula.


why is that?

 

[NBVC]

It seems to be the Text() function causing the problem...probably exceeding the max 7 nested functions limit.


It works if you don't include that function:

=MIN(IF(INDIRECT("A2:A4000")<>"",IF(ISNUMBER(MATCH(DATE(2000+(100*LEFT(INDIRECT("$A$2:$A$4000"))-100)+MID(INDIRECT("$A$2:$A$4000"),2,2),MID(INDIRECT("$A$2:$A$4000"),4,2),MID(INDIRECT("$A$2:$A$4000"),6,2)),J$1:J1+0,0)),"",DATE(2000+(100*LEFT(INDIRECT("$A$2:$A$4000"))-100)+MID(INDIRECT("$A$2:$A$4000"),2,2),MID(INDIRECT("$A$2:$A$4000"),4,2),MID(INDIRECT("$A$2:$A$4000"),6,2)))))

so perhaps you may need to put this formula in a "helper column" and then use the Text() function to reference it and convert it separately.

 

[Fin Fang Foom]

Once again thank You NBVC!