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]

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!

Thank You again It works perfect.

 

[Domenic]

Try...

=SUM(IF(A$2:A$18<>"",IF(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))=C2,B$2:B$18)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Fin Fang Foom]

Try...

=SUM(IF(A$2:A$18<>"",IF(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))=C2,B$2:B$18)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Thank You!

 

[Fin Fang Foom]

Hi Domenic,

I would like to convert my dates as text dates.


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


The formula above is not working is there a way we can have it to return as text dates?

 

[Domenic]

I just tried the formula and it seems to work fine. What happens when you try it?

 

[Fin Fang Foom]

I just tried the formula and it seems to work fine. What happens when you try it?

Thank You for replying Domenic.

The formula you provided works perfectly. But when I tried to modified it to have it as in text dates it repeats the first date when I fill down the formula.


Example:

Foundry Weekly DownTime.xls
	A	B	C	D
1	DATE
2	1060926	9/25/2006	9-25-2006
3	1060927	9/26/2006	9-25-2006
4	1060927	9/27/2006	9-25-2006
5	1060927	9/28/2006	9-25-2006
6	1060925	9/29/2006	9-25-2006
7	1060925	1/0/1900	9-25-2006
8	1060925
9	1060925
10	1060925
11	1060925
12	1060926
13	1060926
14	1060926
15	1060926
16	1060926
17	1060926
18	1060926
19	1060926
20	1060926
21	1060926
22	1060926
Downtime Data

 

[NBVC]

It seems to work if you change the lookup range C$1:C1 to B$1:B1 (I think because now the items above each entry are text...it no longer works as expected).

Your formula now:

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

The bold part is what to change:

=TEXT(MIN(IF(A$2:A$545<>"",IF(ISNUMBER(MATCH(DATE(2000+(100*LEFT(A$2:A$545)-100)+MID(A$2:A$545,2,2),MID(A$2:A$545,4,2),MID(A$2:A$545,6,2)),B$1:B1,0)),"",DATE(2000+(100*LEFT(A$2:A$545)-100)+MID(A$2:A$545,2,2),MID(A$2:A$545,4,2),MID(A$2:A$545,6,2))))),"MM-DD-YYYY")

 

[Fin Fang Foom]

Thanks for replying NBVC,



I still get the first date repeated.

Foundry Weekly DownTime.xls
	A	B	C	D
1	DATE
2	1060926	09-25-2006
3	1060927	09-25-2006
4	1060927	09-25-2006
5	1060927	09-25-2006
6	1060925	09-25-2006
7	1060925	09-25-2006
8	1060925
9	1060925
10	1060925
11	1060925
12	1060926
13	1060926
14	1060926
15	1060926
16	1060926
17	1060926
18	1060926
19	1060926
20	1060926
21	1060926
Downtime Data

 

[NBVC]

Sorry....that was stupid of me....In my sample I still had a column B with the original formula so I was referencing it and it was working....

Anyways...you need to convert your text dates to numerical as you go...so just add a +0 to the end of the same lookup array, like so.

=TEXT(MIN(IF(A$2:A$20<>"",IF(ISNUMBER(MATCH(DATE(2000+(100*LEFT(A$2:A$20)-100)+MID(A$2:A$20,2,2),MID(A$2:A$20,4,2),MID(A$2:A$20,6,2)),B$1:B1+0,0)),"",DATE(2000+(100*LEFT(A$2:A$20)-100)+MID(A$2:A$20,2,2),MID(A$2:A$20,4,2),MID(A$2:A$20,6,2))))),"MM-DD-YYYY")

 

[Fin Fang Foom]

Thanks NBVC that was it the +0


Thanks again!