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

ABCKYC

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.

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.