I am trying to write out a formula, but I can't seem to figure it out or find any help online anywhere.

Here is my formula

=IFERROR(OR(VLOOKUP(M42,PlanningSystemDataLookup!A:X,24,0),(X43>="00/01/1900",""),""))

The formula I used before this, that works is the following

=IFERROR(VLOOKUP(M43,PlanningSystemDataLookup!A:X,24,0)," ")

I used the following formula in a test excel document, which works

=IF(W43>="00/01/1900","",W43)

So, knowing all of this, I want to combine the 2nd and 3rd formula so that the VLOOKUP performs normally as it does, but if the date "00/01/1900" shows in the column, the cell becomes BLANK.

Apologies about this, I looked everywhere, but couldn't find any examples.

Let me know what you think

CodePudding user response：

00/01/1900 is 0: If you do comparisons with numeric dates don't use strings.

Instead of W43>="00/01/1900" use W43>=0

Excel stores dates as numbers (if you do it correctly). Otherwise you would not be able to calculate with dates or use comparisons like greater/smaller to compare the dates.

Numeric dates are stored in Excel as number of days since 1900-01-01 which is represented by 1 because this is considered day one. So 1900-01-02 is the second day and Excel stores it as 2. Today 2022-10-19 is day 44853 since 1900-01-01.

Whenever you use dates make sure they are real numeric dates and not text that looks like a date. With texts you cannot calculate or do comparisons. If you need a specific format for the date you can always use number format to format a numeric date as you want.

For example for 44853 you can make it look like

• 2022-10-19 using the number format YYYY-MM-DD or
• 19/10/2022 using the number format DD/MM/YYYY

So Excel stores the number of days 44853 as value in the cell and just makes it look like a date.