VLOOKUP function returns 1/0/1900

Greeting,
Hope someone can help me w/ this. Some background, I have two worksheets, the first 'Assignments' contains a list of project numbers and the name of the employee assigned to the project, and the second 'Status' contains the project numbers and in either of two columns: 'Complete' (Q) or 'Follow-up' (R) identifies the date the employee finished work on the project (if the date is recorded under follow-up further action is needed).

So, I included a 'Complete' and 'Follow-up" column in the Assignments worksheet and inserted a formula to bring in the date the employee completed the project, specifically:

=IF(ISNA(VLOOKUP(Assignments!B9,'Status'!$A:$A,1,FALSE)),"Open",VLOOKUP(Assignments!B9,'Status'!$A:$Q,17,FALSE)) in the 'Complete' column, and

=IF(ISNA(VLOOKUP(Assignments!B9,'Status'!$A:$A,1,FALSE)),"Open",VLOOKUP(Assignments!B9,'Status'!$A:$R,18,FALSE)) in the 'Follow-up' column

The Status worksheet does not included every project number, only those that the employee has finished, thus if the project number is not listed the formula should return "Open"

The formula works for 95% of the project numbers. But, for a few (and this is why I need help) the formula will correctly bring in the date and recorded it in say the "Complete" column but in the "Follow-up" column where it should be empty the formula instead returns '1/0/1900'

Anyone have any ideas why Excel would be returning '1/0/1900' for only certain cells? Thanks in advance

Try to format Columns as a number

Alright, looking through other posts related to similar problems, I understand that 1/0/1900 is being returned as the VLOOKUP is determining the cell is blank so it is returning a zero value but in this case since cell is formatted as a date it returns 1/0/1900 instead of 0.

So, how do I alter the formula to have a blank value displayed instead of zero value?

If you have formatted column as Number you can use the below forula.

Please Login or Register  to view this content.

If you have not formatted column as Number you can use the below forula.

Please Login or Register  to view this content.

kvsrinivasamurthy-

Thanks you so much! Worked perfectly....thanks again!