Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-01-2011, 11:00 AM
udea udea is offline Use SMALL or LARGE to find date Windows 7 64bit Use SMALL or LARGE to find date Office 2007
Novice
Use SMALL or LARGE to find date
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default Use SMALL or LARGE to find date


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?
Reply With Quote
  #2  
Old 05-01-2011, 03:08 PM
macropod's Avatar
macropod macropod is offline Use SMALL or LARGE to find date Windows 7 32bit Use SMALL or LARGE to find date Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,366
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 05-02-2011, 05:06 AM
udea udea is offline Use SMALL or LARGE to find date Windows 7 64bit Use SMALL or LARGE to find date Office 2007
Novice
Use SMALL or LARGE to find date
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 05-02-2011, 01:43 PM
macropod's Avatar
macropod macropod is offline Use SMALL or LARGE to find date Windows 7 32bit Use SMALL or LARGE to find date Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,366
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
Reply With Quote
  #5  
Old 05-02-2011, 09:08 PM
udea udea is offline Use SMALL or LARGE to find date Windows 7 64bit Use SMALL or LARGE to find date Office 2007
Novice
Use SMALL or LARGE to find date
 
Join Date: Apr 2011
Posts: 13
udea is on a distinguished road
Default

Thank you, it works!
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Use SMALL or LARGE to find date Small font 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
Use SMALL or LARGE to find date Imported message date change to today's date promark Outlook 1 12-23-2005 07:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft