How to Create an Employee Safety Certification Tracker in Excel (Full Transcript)

Speaker 1: I received this question the other day and it says I have been searching high and low for a template to keep track of employees safety tickets and certifications expiry dates. I have approximately 15 people and approximately 10 to 15 different tickets slash certifications dates that need to be tracked. And so I came up with an idea. What I have done is I have made a chart here and we have the employee names down here so you would have up to 15 employees and then I didn't know off the top of my head 15 different certifications but I know that for example it would be first aid, WHMIS and that type of thing. So you would put the different certificates along here. And then in these cells so today is March 3rd. So what I have here is I have the critical dates to you. So the first line is today and then the second line equals I will show you the formula so it is today plus 60 days and so anything that is going to expire within the next 60 days I have conditionally formatted to red and anything on the next one anything that is going to expire within 90 days so it is today plus 90 is yellow. And so what I did is let me show you the formatting. So let's go to conditional format and then what I am going to do is I am going to go manage rules so you can see the rules that I have written down here. So first of all we will look at the red one so what I am going to do is click on this one and I am going to go edit rule so you can see that if it is B5 is less than A2 which is 60 days from today then it is going to turn red. So now let's go back and let's go look at the yellow one and the yellow one says so it is an and so if it is B5 is greater than A2 but less than A3 which is 90 days then it is going to turn yellow and OK and then let's go look at the last one and it equals B5 is greater than or equal to 90 days. So that is how I formatted this to work out. Because I have got my dates out here you can change them at any time. You could change the red to be 30 days and not 60 days. So what we do is we just go to the cell here where it says today equals and you change it to 30 and you can go to this one here that says 90 and you can change it to 180. So you want it to turn yellow if it is within 180 days. And there basically your whole thing turns yellow now. So let's undo this and go back to the way it was. So it is 60 and 90. So that is how you make a certificate tracker for your employees. Please subscribe.