IF / And / Or statement combined

[WillSTARK]

Hi,

I have 2 columns of dates

E and J. At E there is always a date, at J there can be a date or it can also be blank.

I want to say that if a date is <= at any date in E and if a date is <= J or blank, then show as Active, if not Inactive

I came up with this statement

=IF(and(E2 <= DATE(2017,1,1),J2 <= DATE(2017,1,31)or(J2 <>""),"Active","Inactive"))

Yet it does not work.

Any idea? I know I am wrong, but I have been on it for a while and I still can't find the right output

Cheers

 

[AliGW]

Maybe this?

=IF(OR(and(E2<=DATE(2017,1,1),J2<=DATE(2017,1,31)),(J2<>"")),"Active","Inactive"))

 

[Joe4]

The structure would look something like this:

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata](AND[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]E2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]DATE[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]2017[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],OR([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]J2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]DATE[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]2017[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]31[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]),[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]J2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<>[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Active"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Inactive"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]) [/FONT][/COLOR]

Though I am not sure that you have your < and > signs pointed in the right direction, according to your description (your description is not that clear).

 

[WillSTARK]

Hi,

Thanks for your 2 posts. Yet, i do not get the output that I expect.

What I want is:
If date in column E (2016,1,1) is inferior or equal to the first date constraint I set (2017,1,1) and if date in column J (2017,1,20) is inferior or equal to the second date constraint I set (2017,1,30) or if date in J is blank, then show it as Active.

Perhaps I was not clear enough. In fact I want that for any value between (2017,1,1 and 2017,1,31 or blank in J) it shows it as being active.
So it is like a date range


In this function: =IF(OR(and(E2<=DATE(2017,1,1),J2<=DATE(2017,1,31)),(J2<>"")),"Active","Inactive"))
It only returns that if one of the arguments is true then it is Active because when I check, it does not provide the output expected.

Is it clearer?

 

[Joe4]

If you are checking for J2 being blank, you need:

[FONT=Inconsolata][COLOR=#7e3794]J2[/COLOR][COLOR=#000000]=[/COLOR][/FONT][COLOR=green][FONT=Inconsolata]""
[/FONT][/COLOR]

not

[COLOR=#7E3794][FONT=Inconsolata]J2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]<>[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR]

 

[snjpverma]

You can also try this shorter version

=IF((E2<42737)*(J2<42767),"Active","Inactive")

wherein dates are written in number format. The blanks are not to be given a separate condition as it considered as zero by default.

 

[WillSTARK]

Hi,

Finally found the trick,

=if(or(and(E3<=date(2016,12,1),J3>=date(2016,12,31)),J3 =""),"Active","Inactive")

Thks for the previous posts though.