![]() |
#1
|
|||
|
|||
![]() 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? |
#2
|
||||
|
||||
![]()
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] |
#3
|
|||
|
|||
![]()
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 |
#4
|
||||
|
||||
![]()
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] Last edited by macropod; 05-03-2011 at 01:20 AM. |
#5
|
|||
|
|||
![]()
Thank you, it works!
|
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
jpslon1 | Outlook | 1 | 01-10-2011 12:24 AM |
I think my Word vocabulary is too small... | showgie | Word | 0 | 05-14-2010 03:55 PM |
Fonts to small | pljames | Outlook | 0 | 10-09-2007 06:19 PM |
new appointment date always reverts back to today's date | msills | Outlook | 0 | 08-24-2007 08:57 AM |
![]() |
promark | Outlook | 1 | 12-23-2005 07:21 AM |