# 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...
Shums Faruk

Format cell to General and check

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

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

membership
Create an account to see this answer
Signing up is free. No credit card required.
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

membership
Create an account to see this answer
Signing up is free. No credit card required.

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 ""