 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006Return Numeric Values Matching EXACT Date for Criteria
Hi All, I would like a Formula that returns numeric values ONLY if they MATCH the EXACT date. The Formula below does work in part. But when the actual date does not exist or a value for that date does not exist, the Formula returns a value nearest that date. That is, 31/06/2006 does not exist as June only has 30 days. But the Formula retruns a value that has a date 01/07/2006. =INDEX(OFFSET(Data,0,0,,10),MATCH(DATE(YEAR($B22),MONTH($B22),DAY($AB$4)), Date,0),COLUMN(A:A)) The dynamic named range "Data" spans 10 columns and many rows - houses numeric values. I would like to have numeric values returned using an EXACT date for the criteria. "Date" is a single column dynamic range. The values in "Date" are formatted as 11/06/2006. The values in column "B" are also formatted as 11/06/2006. The Day in column $AB$4 can be a single or double- digit: 1, 11, 31 etc. Thanks Sam Does AB4 contain a true date value formatted to display the day, or does it actually contain a one or two digit number. If the former, try replacing... DATE(YEAR($B22),MONTH($B22),DAY($AB$4)) with (TEXT($AB$4,"d")&"/"&TEXT($B22,"mmm/yy"))+0 Otherwise, try... ($AB$4&"/"&TEXT($B22,"mmm/yy"))+0 Note that the formula will return #VALUE! when the date doesn't exist, such as 31/06/2006, and will return #N/A when the date is not found. If so desired, the formula can be amended to trap error values. Hope this helps! > Hi All, > [quoted text clipped - 18 lines] > Thanks > Sam Hi Domenic, Thanks for reply and your assistance. Cell AB4 does just contain a one or two digit number. So, I'm using your second suggestion: >($AB$4&"/"&TEXT($B22,"mmm/yy"))+0 What does the +0 actually do? Cell AB4 contains the two digit number 31 and the month in B22 is June. It returns has #N/A. Should it have returned #VALUE as the 31 June does not exist? =INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0), COLUMN(A:A)) Cheers, Sam >Does AB4 contain a true date value formatted to display the day, or does >it actually contain a one or two digit number. If the former, try >replacing... >DATE(YEAR($B22),MONTH($B22),DAY($AB$4)) >with >(TEXT($AB$4,"d")&"/"&TEXT($B22,"mmm/yy"))+0 >Otherwise, try... >($AB$4&"/"&TEXT($B22,"mmm/yy"))+0 >Note that the formula will return #VALUE! when the date doesn't exist, >such as 31/06/2006, and will return #N/A when the date is not found. If >so desired, the formula can be amended to trap error values. >Hope this helps! > Hi Domenic, > [quoted text clipped - 5 lines] > > What does the +0 actually do? That part of the formula returns the date as a text string. The +0 bit coerces it into a true date value. > Cell AB4 contains the two digit number 31 and the month in B22 is June. It > returns has #N/A. Should it have returned #VALUE as the 31 June does not > exist? > > =INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0), > COLUMN(A:A)) Opening and closing brackets for the lookup value have been omitted. The formula should be as follows... =INDEX(OFFSET(Data,0,0,,10),MATCH(($AB$4&"/"&TEXT($B22,"mm/yy"))+0,Date,0 ),COLUMN(A:A)) Hope this helps! Hi Domenic, Thank you very much for explanation and corrected Formula. That's Great! Cheers, Sam >> Hi Domenic, >[quoted text clipped - 5 lines] >> What does the +0 actually do? >That part of the formula returns the date as a text string. The +0 bit >coerces it into a true date value. >> Cell AB4 contains the two digit number 31 and the month in B22 is June. It >> returns has #N/A. Should it have returned #VALUE as the 31 June does not >> exist? >> =INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0), >> COLUMN(A:A)) >Opening and closing brackets for the lookup value have been omitted. >The formula should be as follows... >=INDEX(OFFSET(Data,0,0,,10),MATCH(($AB$4&"/"&TEXT($B22,"mm/yy"))+0,Date,0 >),COLUMN(A:A)) >Hope this helps! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.