If Then formula help

Copper Contributor

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 Date8/11/19
ItemExpiration Date
Salsa2/2/20
Sweet & Sour1/31/20
Barbeque1/22/20
Soy Sauce3/20/19
Relish2/19/20
Mayo8/21/19
Salt2/6/20
Fry Sauce1/31/20
Dijon Mustard8/10/19
Mrs. Dash5/29/21
3 Replies

@emcilhany

 

Hi,

 

Try this formula:

=IF(AND(B4-$B$1<=30,B4-$B$1>0),"About to expire",IF(B4-$B$1<=0,"Expired",""))

Item Status.png

 

Hope that helps

@Haytham Amairah 

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

@emcilhany

 

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