Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

formula returning 00/00/1900 dates as opposed to blank

Hi Experts using excel 2010

i have the following formula IF(AND(MilestoneName="Parent",GJ6=""),"",VLOOKUP(B6,$B$6:$DU$919,$GI$4,0))

which when the look up works should return back a blank as opposed to 00/00/1900 dates when the referenced column has no dates in it...

can see the error...
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Format cell to General and check
Avatar of route217

ASKER

Showing as 0...but need blanks
Try this formula:
=IF(AND(MilestoneName="Parent",GJ6=""),"",IFERROR(VLOOKUP(B6,$B$6:$DU$919,$GI$4,0),""))

Open in new window

Still seeing 0. not blanks
Try this:
=IFERROR(IF(AND(MilestoneName="Parent",GJ6=""),"",VLOOKUP(B6,$B$6:$DU$919,$GI$4,0)),"")

Open in new window

SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Try this:

=IF(AND(MilestoneName="Parent",GJ6=""),"",IF(ISBLANK(VLOOKUP(B6,$B$6:$DU$919,$GI$4,0)),"",VLOOKUP(B6,$B$6:$DU$919,$GI$4,0)))

When vlookup finds the lookup value but the result is empty, the result of the vlookup is 0 eventhough it really finds a blank. You can check for the blank with ISBLANK function.

Thanks
Rob
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Just notice the cell has custom format of [<1]"";dd-mm-yy
Thanks all solved it appropriate excellent feedback.
Also could try custom format:

dd-mm-yy;;""

Custom formats have 3 sections separated by semi-colon
 Positive - formatted as dd-mm-yy
 Negative - no format as you can't have negative dates
 Zero - formatted as ""
Glad to help.
Just a quick note regarding the accepted results:

Shums solution checks for 3 conditions:
MilestoneName = "Parent"
AND  GJ6 = Blank
AND  VLOOKUP result = 0

With those 3 conditions it will give blank result.

If the VLOOKUP result is not 0 but the other two conditions are met, the result will still be the result of the VLOOKUP.