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

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History