Dates and SMALL Formula

Fin Fang Foom

Well-known Member
Joined
Mar 20, 2005
Messages
598
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
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

Well-known Member
Joined
Mar 20, 2005
Messages
598
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.




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
 

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80

ADVERTISEMENT

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

Well-known Member
Joined
Mar 20, 2005
Messages
598
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,388
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

Well-known Member
Joined
Mar 20, 2005
Messages
598
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
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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,388
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,403
Messages
5,547,750
Members
410,811
Latest member
adustin42
Top