I have a data sheet that has a 13 month break in data that I need to write a formula for. I need it to return "" if the corresponding cell A is blank and I also need to add in that if the date is greater than June 30, 2012 and less than September 30, 2013 then it will return "". Here is what I have, the part in red is the portion I'm having issues with.
=IF($A5="","",If(and($A5>date(2012,6,30),$A5<(2013,9,30))),"",IF(ISERROR(VLOOKUP($A5,Data!$A:$BR,2,0)),0,VLOOKUP($A5,Data!$A:$BR,2,0)))
You are probably having trouble because it's not really a "date" Date's are numbers in Excel. In a blank cell, type = ISNUMBER(A5) to see what happens. What format are your dates in?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
The dates are in the format "Jul-12". I did the IsNumber and it returned "true".
I changed one of them to a more traditional mm/dd/yyyy date and it still give the "formula contained an error" message.
You've got a misplaced ) in there
also missing the 2nd date function
=IF($A5="","",If(and($A5>date(2012,6,30),$A5<(2013,9,30))),"",IF(ISERROR(VLOOKUP($A5,Data!$A:$BR,2,0)),0,VLOOKUP($A5,Data!$A:$BR,2,0)))
Should be
=IF($A5="","",If(and($A5>date(2012,6,30),$A5<date(2013,9,30)),"",IF(ISERROR(VLOOKUP($A5,Data!$A:$BR,2,0)),0,VLOOKUP($A5,Data!$A:$BR,2,0))))
Last edited by Jonmo1; 09-28-2015 at 05:09 PM.
Still giving me the error message
Refresh, I found other issues after my initial post. And edited it to reflect..
Thanks, that worked. Don't know how I missed that but appreciate the help.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks