IF FORMULA FOR GOING-TO-EXPIRY EXPIRD-SOON EXPIRED AND VALID CERTIFICATE

ABCKYC
ABCKYC
edited 11/14/24 in Formulas and Functions

My criteria are the following:

If the Expiration Date Column cell is more then (60) days remaining = “Valid”

If the Expiration Date Column cell is less then (60) days = “Going to Expiry”

IF the Expiration Date Column cell is Less then (30) days = “Expired Soon”

IF the Expiration Date Column cell is between (0) days to (-30) days = “Expired”

IF the Expiration Date Column cell is less then (-30) days = “ Recommendation for Removed from CML"

=IF(L6>EDATE(TODAY(),6),"Fresh",IF(L6<EDATE(TODAY(),6),"N.Expire",IF(L6<=TODAY(),"Expire",""))) =IF(K161>EDATE(TODAY(),2),"Valid",IF(K161<=TODAY(),"Expired","Expired Soon"))

i tired to used this formula but more then argument error i faced. please help me out.

Answers

  • MedaUser
    MedaUser ✭✭✭✭✭

    Hi ABCKYC,

    I'm a little confused by the formula you supplied because I'm not sure what to make of the EDATE formula. My assumption is that it represents the Expiration Date Column cell. Either way, I wrote out a basic syntax of how you should structure your formula with a picture of the sheet outputs from my playground sheet.

    =IF([Primary Column]@row > 60, "Valid", 
    IF([Primary Column]@row > 30, "Going to Expiry", 
    IF([Primary Column]@row > 0, "Expired Soon", 
    IF([Primary Column]@row > -30, "Expired", "Recommendation for Removed from CML"))))
    

    Travis C, PMP

    Smartsheet Leader with 5+ years of SS experience

    If you were happy with my answer, please upvote and mark my response as answered. 

    Travis C, PMP

    Smartsheet Leader with 5+ years of SS experience

    Let's connect: LinkedIn - Travis C.

    If my answer was sufficient, pleaseupvote and mark my response as answered.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!