Calculate the percentage of dates within specific time periods?

Hi All,

Basically what I have is a spreadsheet which contains training modules down the left hand side, and employee names across the top. Once an employee has been trained in a particular module, the corresponding field has a date entered into it which states when the next training date is.

I have conditionally formatted this to warn the user when the refresher training is going to be required.

So
Cell values greater than TODAY()+120 then it's green, the training date is far
Cell values between TODAY()+30 and TODAY()+120 is yellow, meaning it's drawing closer
Cell values between TODAY() and TODAY()+30 is pink, meaning it's very close
Cell values less than TODAY() are red indicating training is overdue

This has been formatted to several sheets for different departments

Now what I want to achieve is a new sheet where I can extract the following information and this is the part I need help with:
- I need the percentage of dates within each of the above criteria for monitoring purposes: i.e. what percentage of training is green, yellow, pink and red
How do I do this and what formula would I need to use?


Thanks in advance

Formulas cannot detect the colour of cells, only VBA can do that.

However you are using Conditional Formatting so just use the same conditions that you used in CF to detect the colour of a cell within a COUNTIF() to count how many of a particular colour there are.
Then you can create a total of colours and calculate a percentage.

Posting a file would help enormously if you need further help.

Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.