Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1268641%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268641%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%202%20formula's%20below%20which%20work%20great%20on%20there%20own%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(J40%26lt%3B%3DTODAY()%2C%22Expired%22%2C%22Live%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(ISBLANK(J31)%2C%22%22%2C%22Pending%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHow%20ever%20I%20am%20trying%20to%20put%20them%20together%20as%20below%20but%20doesn't%20seem%20to%20be%20working%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(ISBLANK(J42)%2C%22%22%2C%22Pending%22%2CIF(OR(J42)%26lt%3B%3DTODAY()%2C%22Expired%22%2C%22Live%22))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20help%20would%20be%20great%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1268641%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268869%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F603027%22%20target%3D%22_blank%22%3E%40Gary-Taylorwimpey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETake%20the%20%22%3CSTRONG%3EOR%3C%2FSTRONG%3E%22%20out.%20That's%20in%20essence%20what%20the%20%22else%22%20clause%20of%20an%20IF%20function%20is%20ALREADY%20asking.%3C%2FP%3E%3CP%3EBy%20nesting%20that%20second%20IF%20in%20the%20first%2C%20you%20are%20%3CEM%3Ealready%3C%2FEM%3E%20saying%2C%20%22on%20the%20other%20hand%2C%20if%20J42%20isn't%20blank%2C%20then%20evaluate%20this%20IF%20clause%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268924%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F603027%22%20target%3D%22_blank%22%3E%40Gary-Taylorwimpey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%20-%20that's%20even%20not%20nested%20IF%2C%203%20arguments%20for%20the%20first%20IF().%20That's%20since%20logic%20is%20not%20defined%20-%20will%20we%20check%20next%20conditions%20if%20J31%20is%20empty%2C%20or%20if%20not%20empty%2C%20or%20what.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271076%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271076%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%20I%20have%20tried%20taking%20out%20the%20OR%2C%20Tried%20lots%20of%20different%20thing%20but%20still%20unable%20to%20get%20it%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20assistance%20anyway%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271166%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F603027%22%20target%3D%22_blank%22%3E%40Gary-Taylorwimpey%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20as%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20correctly%20noted%2C%20in%20addition%20to%20needing%20to%20remove%20the%20redundant%20OR%2C%20you%20hadn't%20correctly%20nested%20the%20second%20IF%20within%20the%20first%2C%20the%20inner%20within%20the%20outer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this.%20I%20just%20did%20and%20it%20worked.%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(J42)%2C%22Pending%22%2CIF(J42%26lt%3B%3DTODAY()%2C%22Expired%22%2C%22Live%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271217%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20great%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that%2C%20worked%20a%20treat.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20Appreciated%20all%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi

 

I have the 2 formula's below which work great on there own

=IF(J40<=TODAY(),"Expired","Live")

=IF(ISBLANK(J31),"","Pending")

How ever I am trying to put them together as below but doesn't seem to be working

=IF(ISBLANK(J42),"","Pending",IF(OR(J42)<=TODAY(),"Expired","Live"))

 

Any help would be great

Thanks

5 Replies
Highlighted

@Gary-Taylorwimpey 

 

Take the "OR" out. That's in essence what the "else" clause of an IF function is ALREADY asking.

By nesting that second IF in the first, you are already saying, "on the other hand, if J42 isn't blank, then evaluate this IF clause"

Highlighted

@Gary-Taylorwimpey 

In addition - that's even not nested IF, 3 arguments for the first IF(). That's since logic is not defined - will we check next conditions if J31 is empty, or if not empty, or what.

Highlighted

@mathetes 

 

Hi 

 

Thanks for the reply. I have tried taking out the OR, Tried lots of different thing but still unable to get it to work.

 

Thanks for your assistance anyway

Highlighted

@Gary-Taylorwimpey

 

Well, as @Sergei Baklan correctly noted, in addition to needing to remove the redundant OR, you hadn't correctly nested the second IF within the first, the inner within the outer.

 

Try this. I just did and it worked.

=IF(ISBLANK(J42),"Pending",IF(J42<=TODAY(),"Expired","Live"))

 

Highlighted

@mathetes

 

That's great

 

Thanks for that, worked a treat.

 

Much Appreciated all