Conditional Formatting formula for middle of month

[Drexl88]

I am using the below formula to highlight cells red when the last day of the month has passed - 2 months after the date occurred. I now want cells to go amber at the mid point of the month - 2 months after the date occurred and green when the date is earlier. any help would be greatly appreciated.

=TODAY()>EOMONTH(G5,2) - red

 

[jasonb75]

2 calendar months or 2 whole months (~60 days)?

e.g. should December dates after the 15th turn amber in mid February or mid March?

 

[Drexl88]

2 calendar months or 2 whole months (~60 days)?

e.g. should December dates after the 15th turn amber in mid February or mid March?

sorry, 2 calendar months. so any date in December amber in mid Feb.

 

[jasonb75]

In that case, try

=TODAY()>(EOMONTH(G5,1)+14)

which will turn TRUE on the 15th.

Make sure that the rule for the red highlight is at the top of the list and that the 'Stop if True' box is checked.

 

[Drexl88]

Thanks. That worked.

Using the same criteria what would the formula look like to count all of the compliant dates in the column. So S4:S79 contains the highlighted dates, how could I count how many of those dates fall inside of the criteria (not red) so how many cells have a date that the end of the second month after it has not passed.

Thanks

 

[jasonb75]

Maybe something like

=SUMPRODUCT(--(S4:S79<=EOMONTH(G4:G79,2)))

This is assuming that S4 is being compared to G4, S5 to G5, etc.

 

[Drexl88]

Maybe something like

=SUMPRODUCT(--(S4:S79<=EOMONTH(G4:G79,2)))

This is assuming that S4 is being compared to G4, S5 to G5, etc.

Apologies, I wasn't clear. The conditional format rule applies to multiple rows/columns (G:R). Column S uses max function to bring the most recent date from each row to the end of the data. So I am trying to count how many dates in column S are compliant with the same rule set in the CF. For example, the date in S5 is earlier than the end of the second month from said date - at the bottom i am trying to calculate total number of dates in column S that meet the criteria. I can upload the data if still not clear.

 

[jasonb75]

A visual example would be helpful, it looks like it should be something fairly simple but I seem to have the concentration of a goldfish with dementia today

 

[Drexl88]

A visual example would be helpful, it looks like it should be something fairly simple but I seem to have the concentration of a goldfish with dementia today

Hi Jason, I managed to resolve it using what you had already provided. Many thanks for you help