So one of the questions that comes up when I am helping customers with this is how do you calculate what percentage of your staff are compliant.
In this scenario, I have set up 16 employees who each have to do a Health and Safety and Induction course. Some of them have done it and some have not. So what percentage of my staff have their courses completed?
Here is the file I start with.
Here is the video that shows you how to do it.
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/Training_records_compliance.mp4″]
You can view the completed file here..
As always, comments and suggestions welcome.
How would you summarize compliance by date completed. For example, a trainee has 30 days from hire to complete training of 20 different SOPs. I have each SOP completion date under the column of the employee. How do we calculate which employees are within their 30 day target due date and which are out of compliance? Is there maybe a formula that can automatically count the number of documents read after the target date?
hi Lia
I would ask the following: Do you want to find out the number of people who have completed within the 30 days, haven’t done any of the courses (completion date) is blank, completion date is after the deadline? Here is a link to a file that covers both the scenario where the course hasn’t been completed and it’s been completed until after the cut-off date.
Let me know how you get on! https://s3.amazonaws.com/excel-training-tracker/Percentage_courses_completed.xlsx
How would you summarize compliance by date completed. For example, a trainee has 30 days from hire to complete training of 20 different SOPs. I have each SOP completion date under the column of the employee. How do we calculate which employees are within their 30 day target due date and which are out of compliance? Is there maybe a formula that can automatically count the number of documents read after the target date?
hi Lia
I would ask the following: Do you want to find out the number of people who have completed within the 30 days, haven’t done any of the courses (completion date) is blank, completion date is after the deadline? Here is a link to a file that covers both the scenario where the course hasn’t been completed and it’s been completed until after the cut-off date.
Let me know how you get on! https://s3.amazonaws.com/excel-training-tracker/Percentage_courses_completed.xlsx
How would you manage if it was a monthly compliance, e.g. testing equipment?