If statement

  • Thread starter Thread starter MHansen
  • Start date Start date
M

MHansen

I have an if statement that looks at the date on another spreadsheet.

=IF(VLOOKUP($A10,Sheet3!$A$1:$H$81,8,0)>="1/31/2008","x"," ")
........
=IF(VLOOKUP($A7,Sheet3!A$1:H$81,8,0)>="12/31/2008","x", " ")

All I want is to return the value of "x' if the date on the other sheet is
greater than or equal to the date referenced in the formula (the formula goes
out all 12 months). The problem I am having is that the formula for January
through September work perfectly but for some reason despite the fact that
the value in column 8 is 6/30/2008, the columns labled October through
December are incorrectly returning the value of "x". Currently the date in
the look up field is formatted as text but I have tried to format it as a
date but it didn't change the results.

Any suggestions???
 
Hi

Your formulae are using two different cells, A7 and A10. Is this
intentional?

If months January to September are working, and October to December aren't,
it sounds as though it is the fact that you are using a single digit for
your month number.
If you are continuing to use Text values in both cases use

=IF(VLOOKUP(TEXT($A10,"dmmyyyy"),Sheet3!$A$1:$H$81,8,0)>="1/31/2008","x","
")

If all values are converted to true Excel dates, then use
=IF(VLOOKUP($A10,Sheet3!$A$1:$H$81,8,0)>=--"1/31/2008","x"," ")

Personally, I would use Null "" rather than Space " " as the alternative
result.
 
I'd use a real date and an empty string:

=IF(VLOOKUP($A10,Sheet3!$A$1:$H$81,8,0)>=date(2008,1,31),"x","")
 
Back
Top