Use SMALL or LARGE to find date

[udea]

Hi,

Just want to ask...

How do I use SMALL or LARGE function to find a day from a list of date?

29/4/2011
29/4/2011
29/4/2011
29/4/2011
30/4/2011
30/4/2011
1/5/2011
1/5/2011
1/5/2011

I tried but it cannot return the value unless i store the date as text or general (format).

Anyone can help?

[macropod]

Hi udea,

If you apply the same cell formatting that you're using in the date cells to the cell containing the LARGE/SMALL formula, the results will appear as dates.

Alternatively, for the dates you gave:
=TEXT(LARGE(A1:A9,2),"d/mm/yyyy")
will return a formatted date for the value ranked as the second-largest. Using LARGE on its own returns the date's serial number.

[udea]

Mr. Macropod,

Thank you, it help me very much.

I tried another way, i have same column with the date list (in sheet1) and make sheet2 to read it (='sheet1'!a1).

when i want to use the SMALL or LARGE function in sheet2 to get date list, nothing appear but if i directly use the function to sheet1, no problem.

1 thing i noticed is that, if a cell in the date list (sheet1) is blank, the linked cell in sheet2 will return 00/01/00 (something like this). is it because of this how to solve this?

Thanks,
Udea

[macropod]

Hi udea,

To use the formula from sheet 2, the syntax sould be:
=LARGE(Sheet1!A1:A9,2)
If you use this formula and the list is empty, you will get:
#NUM!
If you need to supress such errors you need to express the formula like:
=IF(ISERROR(LARGE(Sheet1!A1:A9,2)),"",LARGE(Sheet1 !A1:A9,2))

[udea]

Thank you, it works!