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?

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.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

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))

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you, it works!