# Dates and SMALL Formula

#### Fin Fang Foom

##### Well-known Member
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
ABCDEF
1DATEMIN
210609129/11/2006#NUM!
31060912#NUM!
41060912#NUM!
51060914#NUM!
61060911#NUM!
71060912#NUM!
81060914
91060911
101060911
111060911
121060913
131060911
141060911
151060911
161060911
171060915
181060911
Sheet1

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Care to decompose how 1060911 is a date?

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)

Care to decompose how 1060911 is a date?

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.

Code:
``````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
ABCDEF
1DATEResults desire
210609129/11/20069/11/2006
310609129/11/20069/12/2006
410609129/11/20069/13/2006
510609149/11/2006
610609119/11/2006
710609129/11/2006
810609149/11/2006
91060911
101060911
111060911
121060913
131060911
141060911
151060911
161060911
171060915
181060911
Sheet1

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?

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
ABCDEF
1DATEResults desire
210609129/11/20069/11/2006
310609129/11/20069/12/2006
410609129/11/20069/13/2006
510609149/11/2006
610609119/11/2006
710609129/11/2006
810609149/11/2006
91060911
101060911
111060911
121060913
131060911
141060911
151060911
161060911
171060915
181060911
Sheet1

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.

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!

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
ABCDEFGH
1
2DATEExtend rangeDesire results
310609129/11/2006#VALUE!#NUM!9/11/2006
410609129/12/2006#VALUE!9/11/20069/12/2006
510609129/13/2006#VALUE!9/11/20069/13/2006
610609149/14/2006#VALUE!9/11/20069/14/2006
710609119/15/2006#VALUE!9/11/20069/15/2006
810609121/0/1900#VALUE!9/11/2006
910609141/0/1900#VALUE!9/11/2006
1010609111/0/1900#VALUE!9/11/2006
1110609111/0/1900#VALUE!9/11/2006
1210609111/0/1900#VALUE!9/12/2006
1310609131/0/1900#VALUE!9/12/2006
1410609111/0/1900#VALUE!9/12/2006
1510609111/0/1900#VALUE!9/12/2006
1610609111/0/1900#VALUE!9/13/2006
1710609119/14/2006
1810609159/14/2006
1910609119/15/2006
20
Sheet1

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!

Replies
5
Views
299
Replies
2
Views
393
Replies
0
Views
343
Replies
8
Views
199
Replies
3
Views
361

1,221,384
Messages
6,159,542
Members
451,571
Latest member
Qwissy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back