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",...
Thanks nearly there
So scenarios are:
Completed - If there is only a completed date in the future
Not Completed - if L4, P4 and V4 ALL blank
Pending - If P4 or V4 in future
Expired - if L4 or P4/V4 in past
This formula needs to include Expired and if L4 blank but P4 future should say Pending - see example
I would also need to include somehow if L4 is in the past it brings back 'Expired' too?
So scenarios are:
Completed - If there is only a completed date in the future
Not Completed - if L4, P4 and V4 ALL blank
Pending - If P4 or V4 in future
Expired - if L4 or P4/V4 in past
This formula needs to include Expired and if L4 blank but P4 future should say Pending - see example
I would also need to include somehow if L4 is in the past it brings back 'Expired' too?
=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") - i used =IF(AND(L2701>TODAY(),P2701>TODAY(),P2701>TODAY()),"Completed",
IF(OR(P2701>TODAY(),V2701>TODAY()),"Pending",
IF(AND(ISBLANK(L2701),ISBLANK(P2701),ISBLANK(V2701)),"Not Completed",
IF(OR(L2701<TODAY(),AND(P2701<TODAY(),V2701<TODAY())),"Expired","")))) OliverScheurich Hi, no its not quite right, its showing Expired when all L, P and V are empty and the completed date (L) is in the future
But correct here:
OliverScheurich I tried that but It is only L4, P4 and V4 not N so changed that to the relevant column but where there is not date in either column it is bringing back Expired