• 545K Members
• 8,318 Online
• 649K Conversations

Highlighted
New Contributor

# 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
Highlighted

# Re: If Then formula help

@emcilhany

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

Highlighted

# Re: If Then formula help

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

# Re: If Then formula help

@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