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",...
It seems like you want to check multiple conditions using the IF function to determine different statuses ('Completed', 'Pending', or 'Not Completed') based on various date columns (L, P, and V).
Your current formula is close, but it might need some adjustments to cater to all the conditions you mentioned. Try this updated formula:
=IF(L4>TODAY(), "Completed", IF(AND(P4>TODAY(), V4>TODAY()), "Pending", "Not Completed"))
This formula checks:
- If the date in column L is in the future compared to today, it returns "Completed".
- If both P4 and V4 are in the future compared to today, it returns "Pending".
- If none of the conditions are met, it returns "Not Completed".
This formula assumes that if P4 or V4 is in the future, it is "Pending". Adjust the logic if both shouldn't be considered as "Pending" simultaneously. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
- 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?=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")
