IFS Formula or IF formula

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

SergeiBaklan

Formulas and Functions

Perhaps

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

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

Forum Discussion

IFS Formula or IF formula

3 Replies

Resources