SOLVED

IFS Formula or IF formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2465020%22%20slang%3D%22en-US%22%3EIFS%20Formula%20or%20IF%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465020%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20someone%20can%20assist%20me%3B%3C%2FP%3E%3CP%3EI%20am%20developing%20a%20training%20matrix%20as%20per%20table%20below.%3C%2FP%3E%3CP%3EThe%20status%20field%20has%20the%20current%20formula%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(G7%26lt%3B0%2C%22Expired%22%2CIF(G7%26lt%3B30%2C%22Expiring%20Soon%22%2C%22%22))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20problems%20I%20am%20facing%20is%20despite%20watching%20tutorials%20using%20either%20the%20IF%20nested%20route%20or%20IFS%20function%20I%20cannot%20figure%20out%20how%20to%20include%20if%20G7%26gt%3B30%2C%22Active%22%20-%20this%20would%20show%20training%20certificate%26nbsp%3Bis%20still%20valid.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAlso%2C%20you%20will%20see%20that%20Expiring%20Soon%20is%20coming%20up%20when%20there%20are%20no%20dates%20to%20count%3F%3F%26nbsp%3B%20These%20would%20be%20training%20certificates%20that%20the%20candidate%20is%20not%20required%20to%20complete.%26nbsp%3B%20This%20should%20remain%20Blank.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20anyone%20show%20me%20where%20I%20am%20going%20wrong%20please%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20in%20advance%20for%20your%20time.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22574%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22143%22%3EDate%20completed%3C%2FTD%3E%3CTD%20width%3D%22143%22%3EDate%20Expired%3C%2FTD%3E%3CTD%20width%3D%22161%22%3EDays%20Left%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EStatus%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E14%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E14%2F04%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E-66%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpired%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E10%2F09%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E10%2F09%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E83%3C%2FTD%3E%3CTD%20width%3D%22127%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E15%2F12%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E15%2F12%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E179%3C%2FTD%3E%3CTD%20width%3D%22127%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpiring%20Soon%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpiring%20Soon%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F22%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E298%3C%2FTD%3E%3CTD%20width%3D%22127%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E10%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E10%2F04%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E-70%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpired%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E-67%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpired%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E-67%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpired%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E-67%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpired%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E-67%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpired%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E13%2F04%2F21%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E-67%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpired%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22143%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22143%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22161%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22127%22%3EExpiring%20Soon%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2465020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2465023%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20or%20IF%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1083222%22%20target%3D%22_blank%22%3E%40Sam_Perryman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(G7%26lt%3B0%2C%22Expired%22%2C%0A%20IF(G7%26lt%3B30%2C%22Expiring%20Soon%22%2C%22Active%22))%0A%0A%3DIFS(G7%26lt%3B0%2C%22Expired%22%2C%20G7%26lt%3B30%2C%22Expiring%20Soon%22%2CTRUE%2C%20%22Active%22))%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi 

Hoping someone can assist me;

I am developing a training matrix as per table below.

The status field has the current formula:

=IF(G7<0,"Expired",IF(G7<30,"Expiring Soon",""))

 

The problems I am facing is despite watching tutorials using either the IF nested route or IFS function I cannot figure out how to include if G7>30,"Active" - this would show training certificate is still valid.

 

Also, you will see that Expiring Soon is coming up when there are no dates to count??  These would be training certificates that the candidate is not required to complete.  This should remain Blank.

 

Can anyone show me where I am going wrong please?

 

Thank you in advance for your time.

 

 

 

Date completedDate ExpiredDays LeftStatus
14/04/2014/04/21-66Expired
10/09/2010/09/2183 
15/12/2015/12/21179 
   Expiring Soon
   Expiring Soon
13/04/2013/04/22298 
10/04/2010/04/21-70Expired
13/04/2013/04/21-67Expired
13/04/2013/04/21-67Expired
13/04/2013/04/21-67Expired
13/04/2013/04/21-67Expired
13/04/2013/04/21-67Expired
   Expiring Soon

 

@Sergei Baklan      

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Sam_Perryman 

Perhaps

=IF(G7<0,"Expired",
 IF(G7<30,"Expiring Soon","Active"))

=IFS(G7<0,"Expired", G7<30,"Expiring Soon",TRUE, "Active"))
Thank you Sergei for your time.
BINGO!!
Thank you

@Sam_Perryman , you are welcome