SOLVED

New Contributor

# IFS Formula or IF formula

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?

 Date completed Date Expired Days Left Status 14/04/20 14/04/21 -66 Expired 10/09/20 10/09/21 83 15/12/20 15/12/21 179 Expiring Soon Expiring Soon 13/04/20 13/04/22 298 10/04/20 10/04/21 -70 Expired 13/04/20 13/04/21 -67 Expired 13/04/20 13/04/21 -67 Expired 13/04/20 13/04/21 -67 Expired 13/04/20 13/04/21 -67 Expired 13/04/20 13/04/21 -67 Expired Expiring Soon

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: IFS Formula or IF formula

Perhaps

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

=IFS(G7<0,"Expired", G7<30,"Expiring Soon",TRUE, "Active"))
``````

# Re: IFS Formula or IF formula

Thank you Sergei for your time.
BINGO!!
Thank you

# Re: IFS Formula or IF formula

@Sam_Perryman , you are welcome