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

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

needa 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

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?

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

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

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)]]),"")

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

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.

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

Ask Your Question

Weekly Poll