Forum Discussion
If Then formula help
I am in need of help! I am needing to determine which food items will expire within 30 days of the current date and which items have expired. I want the formula to answer: If the expiration date is within 30 days of the current date then return a value of "expiring soon"; If the expiration date is older than the current date then return a value of "expired";otherwise nothing. Then would like the "expiring soon" and "expired" items to be listed in their own table.
Can someone help? Very much appreciated.
| Current Date | 8/11/19 |
| Item | Expiration Date |
| Salsa | 2/2/20 |
| Sweet & Sour | 1/31/20 |
| Barbeque | 1/22/20 |
| Soy Sauce | 3/20/19 |
| Relish | 2/19/20 |
| Mayo | 8/21/19 |
| Salt | 2/6/20 |
| Fry Sauce | 1/31/20 |
| Dijon Mustard | 8/10/19 |
| Mrs. Dash | 5/29/21 |
3 Replies
Hi,
Try this formula:
=IF(AND(B4-$B$1<=30,B4-$B$1>0),"About to expire",IF(B4-$B$1<=0,"Expired",""))
Hope that helps
Wow! Thank you so very much! This is great, I really appreciate your expert help! Is there a way to add to the formula that if the cell is blank then return a value of "no date on box"?
Kind Regards,
Erin
Hi Erin,
You can update the formula as follows:
=IF(B4="","No date on box",IF(AND(B4-$B$1<=30,B4-$B$1>0),"About to expire",IF(B4-$B$1<=0,"Expired","")))
If you have Excel 2019 or Office 365, you can use https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45?NS=EXCEL&Version=90&SysLcid=1033&UiLcid=1033&AppVer=ZXL900&HelpId=xlmain11.chm60655&ui=en-US&rs=en-US&ad=US instead of Nested IF which is simplified and easier to read:
=IFS(B4="","No date on box",AND(B4-$B$1<=30,B4-$B$1>0),"About to expire",B4-$B$1<=0,"Expired",TRUE,"")
Hope that helps
