I could really use some help please. I don't know if it is even possible but I thought maybe someone here had some advice. I am using Excel 2013. I have created a tracker that has: a list of all personnel in my company going down column A. Going across the top row I have a number of different certificates that are required to be completed, whether annually or semi-annually, etc. For each person that has completed the training I have inserted the date of their completion. (mm/dd/yyyy).
Now in each corresponding column to each certificate I have inserted(using conditional formatting) a formula that turns the date entered green if it is within 365 days(cellvalue=>NOW()-365) or yellow if it is between 335 and 364 and then red if the date is beyond 365 days(for annual certs). Right now I have 89 people in my company. example: cert 1, I have 78 out of 89 personnel in tolerance. 75 green 3 yellow 11 red.
What I am trying to do is get the excel document to automatically update the percentage of personnel that are in tolerance...right now I have to go to each column...sort the dates newest to oldest and then get the count manually and then divide the number in tolerance by 89 for every single certificate. that is very time consuming. I cannot figure out a formula that will do this automatically for me. Does anyone know if this is possible? It would be nice if there was a generic formula that would do it for colors...(take the green and yellow and divide those by 89) but I don't know how to do that. also I thought of trying to nest formulas inside of each other...like date within 365 is true and then take the true and divide that by 89? I don't know if I'm on the right path. Any advice would be greatly appreciated.
Now in each corresponding column to each certificate I have inserted(using conditional formatting) a formula that turns the date entered green if it is within 365 days(cellvalue=>NOW()-365) or yellow if it is between 335 and 364 and then red if the date is beyond 365 days(for annual certs). Right now I have 89 people in my company. example: cert 1, I have 78 out of 89 personnel in tolerance. 75 green 3 yellow 11 red.
What I am trying to do is get the excel document to automatically update the percentage of personnel that are in tolerance...right now I have to go to each column...sort the dates newest to oldest and then get the count manually and then divide the number in tolerance by 89 for every single certificate. that is very time consuming. I cannot figure out a formula that will do this automatically for me. Does anyone know if this is possible? It would be nice if there was a generic formula that would do it for colors...(take the green and yellow and divide those by 89) but I don't know how to do that. also I thought of trying to nest formulas inside of each other...like date within 365 is true and then take the true and divide that by 89? I don't know if I'm on the right path. Any advice would be greatly appreciated.
