We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

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

96 Views
Last Modified: 2017-03-07
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...
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Format cell to General and check
route217Junior

Author

Commented:
Showing as 0...but need blanks
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Try this formula:
=IF(AND(MilestoneName="Parent",GJ6=""),"",IFERROR(VLOOKUP(B6,$B$6:$DU$919,$GI$4,0),""))

Open in new window

route217Junior

Author

Commented:
Still seeing 0. not blanks
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

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

Open in new window

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
Finance Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
route217Junior

Author

Commented:
Just notice the cell has custom format of [<1]"";dd-mm-yy
route217Junior

Author

Commented:
Thanks all solved it appropriate excellent feedback.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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 ""
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Glad to help.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.