Nested IF AND formula

[Mariyka1]

I've tried this in multiple ways but I'm not having much luck. I need to be able to look up two separate columns - one with a date (greater than) and one with text (that contains) - with two different conditions. "Phase 2" would have both of these conditions met, "Phase 1" is anything else.
If anyone can help if at least I'm on the right track per below:


=IF(AND(M4>"08/01/2017",ISNUMBER(SEARCH("A",Q4)),"Phase 2",IF(AND(M4>"12/01/2017",ISNUMBER
(SEARCH("B",Q4))),"Phase 2","Phase 1"))


Any help is appreciated!

 

[Joe4]

The problem is with how you are comparing dates.
If M4 is really a date entry, your formula is comparing it to the text values of "08/01/2017" and "12/01/2017", not the dates.
You can convert those text entries to dates like this:

M4>[COLOR=#ff0000]DATEVALUE[/COLOR]("08/01/2017")

 

[NARAYANK991]

Hi ,

You can also try this :

=IF(AND(M4>"08/01/2017"+0,ISNUMBER(SEARCH("A",Q4))),"Phase 2",IF(AND(M4>"12/01/2017"+0,ISNUMBER(SEARCH("B",Q4))),"Phase 2","Phase 1"))

The addition of 0 (the operation of addition) changes the data type of the text string "08-01-2017" to a number , and therefore a valid date.

 

[Mariyka1]

The problem is with how you are comparing dates.
If M4 is really a date entry, your formula is comparing it to the text values of "08/01/2017" and "12/01/2017", not the dates.
You can convert those text entries to dates like this:

M4>[COLOR=#ff0000]DATEVALUE[/COLOR]("08/01/2017")

Thank you very much both of your solutions worked!

 

[Mariyka1]

Thank you! this also worked!