Combining IFs AND Ors - Whats wrong with that !

[Frank Bacchus]

I am combining an if statement with ORs and ANDs and getting an error. here is what I am getting at:

if D40 is equal to Open or Monitoring (D40 contains Open) and the date value of M40 (M40 contains (04/23/2016) is less than or equal to 5 days from today's date, place a "Y" in the cell else place nothing. Here is my formula:


=IF(OR(D40="Open", D40="Monitoring"), IF(VALUE(RIGHT(M40,10))-VALUE(TODAY())<=$X$1,"Y",""))

Any help?

frank

 

[Villalobos]

Hi,

Maybe this is that formula what you are looking for?

 =IF(AND(OR(D40="Open",D40="Monitoring"),VALUE(M40)<=VALUE(TODAY()-5)),"Y","")

 

[Shteven]

Try this:
=IF(AND(OR(D40="Open",D40="Monitoring"),VALUE(RIGHT(M40,10))<=(TODAY()+5)),"Y","")

 

[Nebu]

Hi:
Another formula, not tested

=IF(AND(OR(D40={"Open","Monitoring"}),DAY(M40)-DAY(TODAY())<=5),"Y","")

Thanks

 

[Frank Bacchus]

Try this:
=IF(AND(OR(D40="Open",D40="Monitoring"),VALUE(RIGHT(M40,10))<=(TODAY()+5)),"Y","")

Thanks:
There are 2 if conditions (1) if the value in D40 is "Open" or "Monitoring" and If the Date in M10 is <= Today + 5. If that is true, then place a "Y" in the cell. Not sure if that is in the formula you have above. Thanks.

frank

 

[Shteven]

Thanks:
There are 2 if conditions (1) if the value in D40 is "Open" or "Monitoring" and If the Date in M10 is <= Today + 5. If that is true, then place a "Y" in the cell. Not sure if that is in the formula you have above. Thanks.

frank

Yes that is what my formula does. Your original post said M40 held the string with the date, but if you can change it to M10 if that is what you need. My formula broken down:

OR(D40="Open",D40="Monitoring")
This checks for D40 equal to Open or Monitoring.

VALUE(RIGHT(M40,10))<=(TODAY()+5)
This compares today with the last 10 characters in M40, which you said is your date, to see if it is less than or equal to 5 days from today's date. The date input must be exactly 10 characters.

=IF(AND(OR(D40="Open",D40="Monitoring"),VALUE(RIGHT(M40,10))<=(TODAY()+5),"Y","")
This checks the logic of both conditions. If both are TRUE, the cell will display "Y". If at least one condition is FALSE, the cell will display nothing.