Home

If Then formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-799051%22%20slang%3D%22en-US%22%3EIf%20Then%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799051%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20in%20need%20of%20help!%26nbsp%3B%20I%20am%20needing%20to%20determine%20which%20food%20items%20will%20expire%20within%2030%20days%20of%20the%20current%20date%20and%20which%20items%20have%20expired.%26nbsp%3B%20I%20want%20the%20formula%20to%20answer%3A%20If%20the%20expiration%20date%20is%20within%2030%20days%20of%20the%20current%20date%20then%20return%20a%20value%20of%20%22expiring%20soon%22%3B%20If%20the%20expiration%20date%20is%20older%20than%20the%20current%20date%20then%20return%20a%20value%20of%20%22expired%22%3Botherwise%20nothing.%26nbsp%3B%20Then%20would%20like%20the%20%22expiring%20soon%22%20and%20%22expired%22%20items%20to%20be%20listed%20in%20their%20own%20table.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%3F%26nbsp%3B%20Very%20much%20appreciated.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECurrent%20Date%3C%2FTD%3E%3CTD%3E8%2F11%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EItem%3C%2FTD%3E%3CTD%3EExpiration%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESalsa%3C%2FTD%3E%3CTD%3E2%2F2%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESweet%20%26amp%3B%20Sour%3C%2FTD%3E%3CTD%3E1%2F31%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBarbeque%3C%2FTD%3E%3CTD%3E1%2F22%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESoy%20Sauce%3C%2FTD%3E%3CTD%3E3%2F20%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERelish%3C%2FTD%3E%3CTD%3E2%2F19%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMayo%3C%2FTD%3E%3CTD%3E8%2F21%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESalt%3C%2FTD%3E%3CTD%3E2%2F6%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFry%20Sauce%3C%2FTD%3E%3CTD%3E1%2F31%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDijon%20Mustard%3C%2FTD%3E%3CTD%3E8%2F10%2F19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMrs.%20Dash%3C%2FTD%3E%3CTD%3E5%2F29%2F21%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-799051%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799070%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Then%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390502%22%20target%3D%22_blank%22%3E%40emcilhany%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(AND(B4-%24B%241%26lt%3B%3D30%2CB4-%24B%241%26gt%3B0)%2C%22About%20to%20expire%22%2CIF(B4-%24B%241%26lt%3B%3D0%2C%22Expired%22%2C%22%22))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126541i31232CB8FB75B0B2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Item%20Status.png%22%20title%3D%22Item%20Status.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799999%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Then%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow!%26nbsp%3B%20Thank%20you%20so%20very%20much!%26nbsp%3B%20This%20is%20great%2C%20I%20really%20appreciate%20your%20expert%20help!%20Is%20there%20a%20way%20to%20add%20to%20the%20formula%20that%20if%20the%20cell%20is%20blank%20then%20return%20a%20value%20of%20%22no%20date%20on%20box%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%2C%3C%2FP%3E%3CP%3EErin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-800194%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Then%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-800194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390502%22%20target%3D%22_blank%22%3E%40emcilhany%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Erin%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20update%20the%20formula%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%20%3DIF(B4%3D%22%22%2C%22No%20date%20on%20box%22%2CIF(AND(B4-%24B%241%26lt%3B%3D30%2CB4-%24B%241%26gt%3B0)%2C%22About%20to%20expire%22%2CIF(B4-%24B%241%26lt%3B%3D0%2C%22Expired%22%2C%22%22)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20Excel%202019%20or%20Office%20365%2C%20you%20can%20use%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fifs-function-36329a26-37b2-467c-972b-4a39bd951d45%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60655%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EIFS%20function%3C%2FA%3Einstead%20of%20Nested%20IF%20which%20is%20simplified%20and%20easier%20to%20read%3A%3C%2FP%3E%3CPRE%3E%3DIFS(B4%3D%22%22%2C%22No%20date%20on%20box%22%2CAND(B4-%24B%241%26lt%3B%3D30%2CB4-%24B%241%26gt%3B0)%2C%22About%20to%20expire%22%2CB4-%24B%241%26lt%3B%3D0%2C%22Expired%22%2CTRUE%2C%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
emcilhany
New 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

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