Forum Discussion
Determine training compliance with IF/IFS function(s)
Hi, I wonder if someone can help with the following:
I'm trying to determine mandatory training compliance for the first quarter of 24/25 and an excerpt of my massive spreadsheet looks like the below:
| Training Level | Latest eLearning | Latest Level 1 | Refresh by: | Latest Level 2 | Refresh by: | Latest Level 3 | Refresh by: | Compliance |
| 1 | 12/09/2023 | |||||||
| 2 | 31/05/2023 | 19/01/2021 | 19/01/2024 | |||||
| 3 | 08/12/2020 | 19/01/2021 | 19/01/2024 | |||||
| 4 | 09/01/2024 | 21/02/2022 | 21/02/2025 |
We have got four different levels of training, and each employee is allocated to a level according to their role. The spreadsheet references their level of training, the latest date that they attended their relevant session and when they are due to refresh their training (except eLearning which is not refreshed).
I need to work out a formula for the 'compliance' column to show whether the employee is currently compliant or not. So what I am trying to test is: if the employee is level x and they are due to refresh their training on date y are they compliant on 01/07/2024? For the staff who are designated eLearning only, the formula only needs to take into consideration whether there is a date in the relevant column earlier than 01/07/2024.
I have tried variations of the IF/IFS formula including AND and OR functions and various IF formulas nested into each other, but I can't seem to be able to write a formula that will differentiate between 'compliant' and 'not compliant' answers. I keep getting one or the other!
Any suggestions will be very much appreciated, thank you!
Anastasia
- It would be helpful if you could write down what result you expect in the individual columns for the Compliance column and explain why this is the case. You have described 4 training levels in the rows. However, only level 3 can be found in the columns. Is level 4 the eLearning?
- Hi, thanks for the response.
Yes, the fourth level is eLearning - although in reverse, so the training pathway for an employee is eLearning, Level 1, Level 2, Level 3 - so four levels in total. Levels 1, 2 and 3 are refreshed every three years from the date of completion. Each employee is allocated a level and they have to attend the course that is relevant to their level (some will have attended training at different levels, esp if they have progressed through the years and their role has changed but the report is only looking at the allocated level at the point of reporting). They then have to refresh this course every three years (except eLearning which is not refreshed). If they don't attend the course again before the three years lapse, they become non-compliant. The training cycle runs from April to March each year and I produce compliance reports every quarter. So my spreadsheet contains a column with the employee's training level and then columns with the different levels and the 'compliance' column at the end. The compliance column should contain two words either 'compliant' or 'not compliant'. So my formula will need to test the following: for those allocated level 1 (as per data in 'Training Level' column) whether the date in the 'eLearning' column is the same or earlier than 30/06/2024 and for those allocated levels 2, 3 or 4 respectively, whether the relevant dates in the 'Refresh by' columns are later than 30/06/2024. As mentioned, if the relevant conditions are true, the result in column 'Compliance' will be Compliant, if not, the result will be 'Not Compliant'.
The latest iteration of the IF formula I have tried is: =IF(IF(M2>DATEVALUE("30/06/2024"),4,IF(K2>DATEVALUE("30/06/2024"),3,IF(I2>DATEVALUE("30/06/2024"),2,IF(G2<=DATEVALUE("30/06/2024"),1,0))))>=F2,"Compliant","Not Compliant")
Unfortunately, it is not giving me consistent results, so I suspect I must have made a mistake somewhere.
Here's an example:
Training Level Latest eLearning Latest Level 1 Refresh by: Latest Level 2 Refresh by: Latest Level 3 Refresh by: Compliance
2 15/02/2021 30/10/2020 30/10/2023 Compliant
4 15/03/2021 27/11/2020 27/11/2023 Not Compliant
Whereas the formula returns the correct result for the level 4 employee, it doesn't do so for the level 2 employee. They both should have been 'not compliant'.
I hope my explanation makes sense and thank you for taking the time to read this long message!- After posting my reply, I realised the example I tried to copy has been completely messed up in terms of format! I am trying to post a screenshot of the spreadsheet but can't seem to be able to do that either! Apologies, I hope you are able to see the inconsitency in the results of the formula
