Only perform an if statement if the contains a date and ignore other data

[Engineerlee]

Hi,

I have a cell A1
this contains a formula to pull a date from another worksheet using an Index and Match Formula

In Cell A2 I want to perform a check to see if that date is greater than or less than today.
The problem I have is that my formula in Cell A2 is seeing the formula in A1 and always returns a value even if no date is displayed.

This is my formula in A2
<today(),"overdue","not due")<today(),"overdue","not="" due")
<today(),"overdue","not due")
<today(),"overdue","not due")="" "
IF(K25<TODAY(),"OVERDUE","NOT DUE")<today(),"overdue","not due")
<today(),"overdue","not due")


Any advice.</today(),"overdue","not></today(),"overdue","not></today(),"overdue","not></today(),"overdue","not></today(),"overdue","not>

 

[Fluff]

If you change the value in F25, does the date change?

 

[Eric W]

The custom format doesn't change what's in the cell, only how it's presented. Your A1 formula returns a "" if the MATCH fails, so the formula from post 9 should work.

Another possibility is that the MATCH finds something in D:D, but the corresponding cell in Q:Q has some text or a negative number. Your custom format would hide those. To check that out, find an empty cell somewhere, with no custom formatting, and enter
=A1
If it shows something, then you need to check your data on the 1.1 Centre sheet.

 

[Engineerlee]

Hi Fluff,

If I change it to look up another value and that reference finds a date then yes

The formula returns 00/01/1900 into A1 as the reference in F25 has nothing in '1.1 Centre '!$Q:$Q,MATCH
so to stop this value I added the cell formatting

 

[Engineerlee]

Hi Eric,

I may have part answered your point when responding to Fluff.

So the detail in F25 does match an entry in D:D
However in some cases there isnt a value in Q:Q

If thee isnt a value in Q:Q I get 00/01/1900 returned. To stop this I formatted the cell dd/mm/yyy;;

so I think my problem is the returning of the default value 00/01/1900 which I am hiding but the formula
=IF(A1="","",IF(A1 < TODAY(),"OVERDUE","NOT DUE"))
still acknowledges this and therefore I always get Overdue returned.

If this is the case, how do I stop the 00/01/1900 being returned to cell A1

Cheers

 

[Fluff]

How about
=IF(OR(A1="",A1=0),"",IF(A1 < TODAY(),"OVERDUE","NOT DUE"))

 

[Engineerlee]

Fluff,

=IF(OR(A1="",A1=0),"",IF(A1 < TODAY(),"OVERDUE","NOT DUE"))

This works perfectly.

Thanks to you and Eric for your help on this matter and providing the solution

 

[Fluff]

Glad we could help & thanks for the feedback