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 LevelLatest eLearningLatest Level 1Refresh by:Latest Level 2Refresh by:Latest Level 3Refresh by:Compliance112/09/2023       231/05/202319/01/202119/01/2024     308/12/2020    19/01/202119/01/2024 409/01/2024  21/02/202221/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

​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 LevelLatest eLearningLatest Level 1Refresh by:Latest Level 2Refresh by:Latest Level 3Refresh by:Compliance112/09/2023       231/05/202319/01/202119/01/2024     308/12/2020    19/01/202119/01/2024 409/01/2024  21/02/202221/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     Read More

​