I'm trying to use EDATE in conditional formatting to turn cells different colours and then use COUNTIF to count green and turn this into a percentage

[TrainingManager999]

The conditioning formatting worked when using "cells that contain" but other forums say that it has to be a formula to then use count function. Please can someone help without using macros as I don't understand these.

 

[Fluff]

Hi & welcome to MrExcel.
Can you please explain what you are trying to do.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Redirecting

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[TrainingManager999]

Hi & welcome to MrExcel.
Can you please explain what you are trying to do.

Hi. The dates are completion dates of training and this training expires in 12 months of that date. I want the cells to be green if they are within date, amber if they are coming up to expiring within 2 months and red if they have expired. This worked when I did it with "cell contains" but when I switched it to a formula, it is just showing red everywhere. That's the first problem. Then what I need to do is show a percentage of dates in date ie in green against the total number of staff, showing how many people are compliant. I read that this was why the conditional formatting needed to be a formula for this to work but I can't get it to work.

 

[Fluff]

The conditional formatting has nothing to do with showing percentages, added to which a formula cannot "see" formatting of any sort.
You can get the count by using

=countifs(c4:c33,">="&edate(today(),9))

 

[TrainingManager999]

The conditional formatting has nothing to do with showing percentages, added to which a formula cannot "see" formatting of any sort.
You can get the count by using

=countifs(c4:c33,">="&edate(today(),9))

Hi. That code comes back with a zero

 

[Fluff]

Do you have any dates that are greater than 9 months from today? (ie 6th Jan 2024 or more)

 

[TrainingManager999]

Do you have any dates that are greater than 9 months from today? (ie 6th Jan 2024 or more)

Yes. The training lasts for 12 months from the date entered into the spreadsheet

 

[TrainingManager999]

Yes. The training lasts for 12 months from the date entered into the spreadsheet

Hi. Its works. I needed to put a minus in front of the 9 to make it work.
Thank you so much for your help

 

[Fluff]

Your'e welcome & thanks for the feedback.