• 467K Members
• 11.5K Online
• 565K Conversations

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

# Re: If Then formula help

@emcilhany

Hi,

Try this formula:

Hope that helps

# 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies