Excel 2007 : Conditional Formatting based on a moving date range

**milliemoo** · 10-03-2011, 08:10 AM

Hello, Please can someone help me with some conditional formatting that I am stuck on?

I have a spreadsheet that monitors complaints received from customers. In column H, I have the date on which the complaint was logged. In column Z I have the category of the complaint. I need to format the cells in column Z to highlight red if that specific category appears in the column more than twice within a 30 day period. I'm not sure how to write the formula in CF so that I can apply it to the entire column. So, the purpose of this is so that I can recognise if a trend is occurring and a specific category of complaint is occurring more than three times within a rolling 30 day period. I hope this makes sense. Any help much appreciated.

**daddylonglegs** · 10-03-2011, 09:12 AM

select column Z and apply this formula in conditional formatting

=COUNTIFS(Z:Z,Z1,H:H,">"&H1-30,H:H,"<"&H1)=2

that will apply formatting to the 3rd entry (chronologically) for a specific complaint category within any 30 day period

**milliemoo** · 10-03-2011, 09:59 AM

Originally Posted by daddylonglegs

select column Z and apply this formula in conditional formatting

=COUNTIFS(Z:Z,Z1,H:H,">"&H1-30,H:H,"<"&H1)=2

that will apply formatting to the 3rd entry (chronologically) for a specific complaint category within any 30 day period

It works! Thank you so much.....and I can figure out how you've done it as well which means I should be able to replicate! Thank you very much.

**milliemoo** · 10-03-2011, 10:30 AM

Please can you advise how I could tweak this so that all three (or more) instances are highlighted, instead of just the third?

Thank you

**milliemoo** · 11-15-2011, 11:09 AM

Hello, As per the thread below, I now have a formula that highlights the third occurence of a particular entry within a 30 day period. However, I need it to highlight all three occurences, not just the third. Please can you advise if this is possible? To clarify, my conditional formatting is as follows:

=COUNTIFS(Z:Z,Z1,H:H,">"&H1-30,H:H,"<"&H1)=2

but this only highlights the third occurence, not all three.

Many Thanks

**daddylonglegs** · 11-15-2011, 11:23 AM

OK try this version to format all instances or each category that have another instance of that category within 30 days

=COUNTIFS(Z:Z,Z1,H:H,">="&H1-30,H:H,"<="&H1+30)=2

**milliemoo** · 11-15-2011, 11:34 AM

Hello, and thank you for the quick response. I'm afraid it doesn't appear to be working. I have amended the layour slightly so that the date received is in now in column E and the category is in column J. So, I have used this formula in CF:

=COUNTIFS(J:J,J1,E:E,">="&E1-30,E:E,"<="&E1+30)=2

Shall I attach some of the data to show you?

**daddylonglegs** · 11-15-2011, 12:19 PM

Oh, sorry I meant to use >= rather than = so should be like this

=COUNTIFS(J:J,J1,E:E,">="&E1-30,E:E,"<="&E1+30)>=2

**milliemoo** · 11-15-2011, 12:29 PM

Perfecto! I've tried to add to your reputation but it won;t let me do it again! Thank you so much anyway. I'm learning loads completing this piece of work!