Forum Discussion

SGeorgie's avatar
SGeorgie
Brass Contributor
Nov 13, 2023

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",...
Excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Gold Contributor

SGeorgie 

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.

SGeorgie's avatar
SGeorgie
Brass Contributor
Nov 13, 2023
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?

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 13, 2023

    SGeorgie 

    =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?

     

     

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Nov 14, 2023

      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

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Nov 14, 2023

      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: 

       

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Nov 14, 2023
      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",""))))

Resources