Solved VLOOKUP Returns 00/01/1900 Instead Of Blank

January 24, 2018 at 20:15:18
Specs: Windows 7
Hi all,

I have a formula which was originally showing #N/A as well as 0/01/1900.

I am using Excel 2016

I was able to get rid of the #N/A but I cannot get rid of the 0/01/1900 any suggestions would be awesome

The formula I am using is as follows

=IFERROR(VLOOKUP($A2,'CoP Expiry'!$A$2:$B$6000,2,FALSE),"")


See More: VLOOKUP Returns 00/01/1900 Instead Of Blank

Report •

#1
January 25, 2018 at 10:19:35
Since I know nothing about your data, I have to make a few assumptions here:

1 - The cell with the VLOOKUP is formatted as a date
2 - Your date format is DD/MM/YYYY
3 - The value being returned by VLOOKUP is sometimes 0 (zero)

If those assumptions are correct the issue is that entering/returning a 0 in a cell that is formatted as a date returns the very first day of the Excel calendar, which is January 0, 1900.

I can think of 2 options:

1 - Eliminate the zeros from the data set on the 'CoP Expiry' page so that a 0 is never returned.

2 - Stop Excel from turning the 0 into a date by forcing a "" (empty string) if VLOOKUP returns #N/A or 0:

=IF(OR(ISNA(VLOOKUP($A2,'CoP Expiry'!$A$2:$B$6000,2,0)),
VLOOKUP($A2,'CoP Expiry'!$A$2:$B$6000,2,0)=0),"",
VLOOKUP($A2,'CoP Expiry'!$A$2:$B$6000,2,0))

Now, if you actually need a 0 to be displayed in the cell then let us know and we'll work on that.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
January 29, 2018 at 23:14:48
Hi there DerbyDad03,

Thanks for your advice.

Further info is that the VLookup is a pay number and it is searching another worksheet for expiry dates

I have tried your formula and it has gotten rid of the 1/1/1900 issue but the #N/A is still there.

Any ideas?


Report •

#3
January 30, 2018 at 06:53:32
✔ Best Answer
Try this:

=IF(ISNA(VLOOKUP($A2,'CoP Expiry'!$A$2:$B$6000,2,0)),"",IF(VLOOKUP($A2,'CoP Expiry'!$A$2:$B$6000,2,0)=0,"",VLOOKUP($A2,'CoP Expiry'!$A$2:$B$6000,2,0)))

Excel didn't like it if the VLOOKUP returned #N/A within the OR. The #N/A error took precedence over the OR.

Just a tip:

If you had used the Formula Evaluator feature you would have seen that.


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
January 30, 2018 at 13:42:11
DerbyDad03

You are a champion, that fixed it. Thanks a million.


Report •

#5
January 30, 2018 at 14:20:31
So, I am 99.9% there as I have found one other issue which is similar to my previous

I have duplicated your solution across three columns and it works perfectly (columns G, H & I)

But in Column J, I am looking to fill the cell with the most recent date columns G, H & I

EG G2 = 27/07/2019, H2 = Blank & I2 = 30/10/2020 then I would want J2 to equal 30/10/2020 as it is the latest date in the range however, I am still getting 0/01/1900 in the J column where all of the G,H & I cells are empty

The formula that I am using is

=IFERROR(MAX(Table2[@[CoP Expiry]:[ACV expiry (Comms Only)]]),"")


Report •

#6
January 30, 2018 at 16:30:23
I'm not sure why you are using IFERROR.

What error are you trying to hide/avoid?

If I start with this (using my date format)

	
             G                 H                 I	       J
2	7/27/2019                            10/30/2020	   =MAX(G2:I2) 

I get this: 10/30/2020

Isn't that what you want?


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#7
January 30, 2018 at 16:49:48
Sorry,

What I am getting is 0/01/1900 when G, H & I are ALL blank. This happens when the individual hasn't completed ANY training at all for those fields.

I am not getting the error where there is at least one date entered for the columns but I am when ALL of the cells are blank.


Report •

#8
January 30, 2018 at 17:08:08
Think about the logic of why Excel returns 0/1/1900 when all cells are blank. As noted earlier, if a cell formatted as a Date contains a zero, Excel will returns the start of time as it knows it: January 0, 1900

What is the MAX value in 3 blank cells? What is the MIN value? What is the SUM? The answer is Zero.

Zero isn't an error, it's a valid value, so IFERROR isn't going to help.

Just write a simple IF function to return "" when MAX returns zero and the MAX date when it doesn't.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Ask Question