Aug 11 2019 08:25 AM
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 |
Aug 11 2019 08:48 AM
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
Aug 12 2019 07:13 AM
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
Aug 12 2019 08:30 AM
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 IFS function 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