# Dates and Excel Microsoft Microsoft office excel 2007 ac...
March 12, 2010 at 12:19:55
Specs: Windows XP
 I have a sheet in excel which I input my daily results. Some dates or blocks of dates are missing as I am unable to gather the data. (vacation, travel, etc)Column A is the date (mm/dd/yyyy)Column B is the result (0.00)I have another sheet with the following:Column A has a list of all dates (month and day only) (366 rows) Columns B through M have the year in row 1I need to lookup the month and the day and attach the year based on the column year (this is easy)=VLOOKUP(DATEVALUE(MONTH(\$A2)&"/"&DAY(\$A2)&"/"&YEAR(C\$1)),Generation!\$B:\$C,2)BUT I need the exact date data returned. VLOOKUP returns the closest lower value.If no exact date then return 0 or "".Is there an easy way to do this in Excel without writing code?Thanks for helping in advance! See More: Dates and Excel

#1 March 12, 2010 at 12:32:23
 If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookup argument:=VLOOKUP(DATEVALUE(MONTH(\$A2)&"/"&DAY(\$A2)&"/"&YEAR(C\$1)),Generation!\$B:\$C,2,0)Review the VLOOKUP Help file to see the options for this argument.If an exact match isn't found, this will return #N/A, so wrap the formula in an IF(ISNA()) function to get a "" of 0 if the VLOOKUP evaluates to #N/A. This will return "":=IF(ISNA(VLOOKUP(DATEVALUE(MONTH(\$A2)&"/"&DAY(\$A2)&"/"&YEAR(C\$1)),Generation!\$B:\$C,2,0)),"",VLOOKUP(DATEVALUE(MONTH(\$A2)&"/"&DAY(\$A2)&"/"&YEAR(C\$1)),Generation!\$B:\$C,2,0))

Report •

#2
March 12, 2010 at 13:51:48
 Derbydad03This solved my problem!! - Never used the False argument with VLOOKUP - therefore wasn't looking in this direction.To solve my Leap year (Feb 29) issue, I used ISERROR vs ISNA.I also added further to this statement to solve where the date had other data but no daily input (blank cell) This kept the formula from returning a 0 when the date was there. It's a little long but returns the correct data.=IF(ISBLANK(VLOOKUP(DATEVALUE(MONTH(\$A7)&"/"&DAY(\$A7)&"/"&YEAR(B\$1)),Generation!\$B:\$C,2,FALSE)),"",IF(ISERROR(VLOOKUP(DATEVALUE(MONTH(\$A7)&"/"&DAY(\$A7)&"/"&YEAR(B\$1)),Generation!\$B:\$C,2,FALSE)),"",VLOOKUP(DATEVALUE(MONTH(\$A7)&"/"&DAY(\$A7)&"/"&YEAR(B\$1)),Generation!\$B:\$C,2,FALSE)))Thanks again!!

Report •

#3 March 12, 2010 at 17:48:36
 Hi,I don't know if this is of any use to you, but to find if the year is a leap year use:`=DAY(EOMONTH(DATE(YEAR(TODAY()),"2","1"),0))`For leap years it will return 29 and 28 for other yearsIf EOMONTH() does not work check to see that you have the Analysis Toolpak Add-In loaded.From the Office Button, click Excel Options (at bottom)Select the AddIns Tab and at the bottom make sure the Manage Add-Ins drop-down shows Add-Ins and click Go.In the dialog box make sure that the box is checked alongside Analysis Toolpak(As I don't have Excel 2007 on this PC, those instructions are from memory, so the names on Tabs etc. may not be absolutely correct).Regards

Report •
Related Solutions 