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...
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