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
5 Replies
ty so much for your quick reply. i tried my best to follow you, im not all that computer savvy, but im mashing keys and trying.. I do have a question tho if i may, Im working off an idea pad computer, the battery is internal, requires me to dismantle the laptop to get to it. but back to my question, at the end you mentioned unplugging device and so i took that to mean kill all the power sources, let it rest a few minutes and then reinsert batt put laptop together and then plug it back in... thats exactly what i did., Is this correct thinking??? or did i do something wrong? i ask this cuz the procedure didnt take to the machine. i plugged her back up then plugged in usb device {the burner} and I recieved that dreaded msg all over again. then went to device mngr and sure enough all that work we did, was gone. And back to orig error msg. so by me unplugging batt taking laptop apart, did that do something to negate our work? {it's only popping the back off laptop to access plug for batt}.. so maybe i didn't do something right while I was remapping it?? I came out the same place you did and had the exact same address you did, that's what I`ll call it an address or map to the drive. then I hit enter key, then closed each window individually that I had open, then I shut down the laptop, then unplugged everything, flipped it, and took the back off and unplugged the batt. left it alone for abt 3 or 4 mins and put it back together etc etc.. is there something I` thatm doing wrong? leave the batt alone or?? I appreciate the help and I`ve tried everything else, I`m almost certain if I can get this to take, that It`ll work fine, at least that`s what`s dancing around in my head, along with the bewilderment of attempting that procedure again. lol
Btw, thank you for that video, it does help us non computer people get by and even fix things. We need all the help and visuals we can get, at least I do.
- 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
