Jun 19 2021 01:47 PM - edited Jun 19 2021 01:52 PM
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 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 |
Jun 19 2021 01:51 PM
SolutionPerhaps
=IF(G7<0,"Expired",
IF(G7<30,"Expiring Soon","Active"))
=IFS(G7<0,"Expired", G7<30,"Expiring Soon",TRUE, "Active"))
Jun 19 2021 01:51 PM
SolutionPerhaps
=IF(G7<0,"Expired",
IF(G7<30,"Expiring Soon","Active"))
=IFS(G7<0,"Expired", G7<30,"Expiring Soon",TRUE, "Active"))