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

 

[Aladin Akyurek]

Care to decompose how 1060911 is a date?

 

[Doug.T]

Try this in C2: (works in my testing)

=DATEVALUE(MID(SMALL($A$2:$A$851,ROWS(A$2:A2)),4,2)&"/"&RIGHT(SMALL($A$2:$A$851,ROWS(A$2:A2)),2)&"/"&LEFT(SMALL($A$2:$A$851,ROWS(A$2:A2)),3)-100)

 

[Fin Fang Foom]

Care to decompose how 1060911 is a date?

Hi Aladin,

The dates are actually from the AS/400 operating system. When I transfer the data from AS/400 to a excel workbook thats how it comes out.

1060911 is...

The 1 represents the century

The 06 represent the year

The 09 represents the month

and the 11 represents the day.

Try this in C2: (works in my testing) 

=DATEVALUE(MID(SMALL($A$2:$A$851,ROWS(A$2:A2)),4,2)&"/"&RIGHT(SMALL($A$2:$A$851,ROWS(A$2:A2)),2)&"/"&LEFT(SMALL($A$2:$A$851,ROWS(A$2:A2)),3)-100)

Hi Doug.T,


I tried your formula and I only get the smallest dates.

Any suggestions?

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

 

[Doug.T]

It should work, but if you have seven 9/11/2006 entries in column A it will show seven 9/11/2006 entries in column C. Does it work if you copy the formula further down?

 

[Fin Fang Foom]

Hi Doug T,


I dont think it will work. Because input this formula in cell C2.

=DATEVALUE(MID(SMALL($A$2:$A$851,3),4,2)&"/"&RIGHT(SMALL($A$2:$A$851,3),2)&"/"&LEFT(SMALL($A$2:$A$851,3),3)-100)

It still gives me:

09/11/2006

it should give me

09/13/2006

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

 

[Aladin Akyurek]

C1 housing the text MIN...

C2:

=MIN(IF(ISNUMBER(MATCH((MID($A$2:$A$18,4,2)&"/"&RIGHT($A$2:$A$18,2)&"/"&SUBSTITUTE(LEFT($A$2:$A$18,3),1,"",1))+0,$C$1:C1,0)),"",(MID($A$2:$A$18,4,2)&"/"&RIGHT($A$2:$A$18,2)&"/"&SUBSTITUTE(LEFT($A$2:$A$18,3),1,"",1))+0))

which you need to confirm with control+shift+enter then copy down.

Not sure whether the date conversion is generic though.

 

[Domenic]

Try...

C2, copied down:

=SMALL(DATE(2000+(100*LEFT(A$2:A$18)-100)+MID(A$2:A$18,2,2),MID(A$2:A$18,4,2),MID(A$2:A$18,6,2)),ROWS(C$2:C2))

...confirmed with CONTROL+SHIFT+ENTER. If the range can contain blanks, try...

=SMALL(IF(A$2:A$18<>"",DATE(2000+(100*LEFT(A$2:A$18)-100)+MID(A$2:A$18,2,2),MID(A$2:A$18,4,2),MID(A$2:A$18,6,2))),ROWS(C$2:C2))

Note that the formulas should take into account the century, as designated by the first digit.

Hope this helps!

 

[Fin Fang Foom]

Hi Aladin,



Your formula works but when I extend the range I get errors. Maybe because there are blanks in the other cells. When I transfer the info into the excel workbook my data flucuates daily. Is it possible to ignore blanks?


Hi Domenic,

Your formula does work but it account for duplicates. Its my fault I should have said uniques in my previous thread. Any way to go around that?

Book2
	A	B	C	D	E	F	G	H
1
2	DATE			Extend range			Desire results
3	1060912		9/11/2006	#VALUE!	#NUM!		9/11/2006
4	1060912		9/12/2006	#VALUE!	9/11/2006		9/12/2006
5	1060912		9/13/2006	#VALUE!	9/11/2006		9/13/2006
6	1060914		9/14/2006	#VALUE!	9/11/2006		9/14/2006
7	1060911		9/15/2006	#VALUE!	9/11/2006		9/15/2006
8	1060912		1/0/1900	#VALUE!	9/11/2006
9	1060914		1/0/1900	#VALUE!	9/11/2006
10	1060911		1/0/1900	#VALUE!	9/11/2006
11	1060911		1/0/1900	#VALUE!	9/11/2006
12	1060911		1/0/1900	#VALUE!	9/12/2006
13	1060913		1/0/1900	#VALUE!	9/12/2006
14	1060911		1/0/1900	#VALUE!	9/12/2006
15	1060911		1/0/1900	#VALUE!	9/12/2006
16	1060911		1/0/1900	#VALUE!	9/13/2006
17	1060911				9/14/2006
18	1060915				9/14/2006
19	1060911				9/15/2006
20
Sheet1

 

[Domenic]

Try...

C2, copied down:

=MIN(IF(A$2:A$18<>"",IF(ISNUMBER(MATCH(DATE(2000+(100*LEFT(A$2:A$18)-100)+MID(A$2:A$18,2,2),MID(A$2:A$18,4,2),MID(A$2:A$18,6,2)),C$1:C1,0)),"",DATE(2000+(100*LEFT(A$2:A$18)-100)+MID(A$2:A$18,2,2),MID(A$2:A$18,4,2),MID(A$2:A$18,6,2)))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!