Forum Discussion
IF FORMULA - Help Needed please
Hi Im trying to write an IF formula and its erroring Basically I this formula worked but i needed the 'not completed' part added! =IF(L4>TODAY(),"Compliant", IF(P4>TODAY(),"Pending",...
=IF(AND(L4>TODAY(),N4>TODAY(),P4>TODAY()),"Completed",
IF(OR(P4>TODAY(),V4>TODAY()),"Pending",
IF(AND(ISBLANK(L4),ISBLANK(N4),ISBLANK(V4)),"Not Completed",
IF(OR(L4<TODAY(),AND(P4<TODAY(),V4<TODAY())),"Expired",""))))
Does this return the intended result?
forgot to mention L, P and V having formulas in them so the columns should be K, O and U
This works to the biggest extent - but some completed Expired ones are saying compliant not Expired....
There are also 2 rows with Pending Expiry as 2023-01-06 which are coming back as Pending but should say Expired (no other dates for that one )
=IFS(OR(AND(ISBLANK(K4),ISBLANK(O4),ISBLANK(U4))),"Not Completed",OR(K4>=TODAY()),"Compliant",OR(AND(K4<TODAY(),O4<TODAY(),U4<TODAY())),"Expired",OR(AND(K4<TODAY(),O4>=TODAY())),"Pending",OR(AND(K4<TODAY(),U4>=TODAY())),"Pending")
This works to the biggest extent - but some completed Expired ones are saying compliant not Expired....
There are also 2 rows with Pending Expiry as 2023-01-06 which are coming back as Pending but should say Expired (no other dates for that one )
=IFS(OR(AND(ISBLANK(K4),ISBLANK(O4),ISBLANK(U4))),"Not Completed",OR(K4>=TODAY()),"Compliant",OR(AND(K4<TODAY(),O4<TODAY(),U4<TODAY())),"Expired",OR(AND(K4<TODAY(),O4>=TODAY())),"Pending",OR(AND(K4<TODAY(),U4>=TODAY())),"Pending")
