IF FORMULA FOR GOING-TO-EXPIRY EXPIRD-SOON EXPIRED AND VALID CERTIFICATE
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 412 Global Discussions
- 221 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 461 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!