Conditional Formatting to highlight expired and upcoming dates

**hongman** · 02-24-2016, 09:06 AM

Hi all

I have a training matrix with names down the left and certifications, qualifications and other details along the top.

I want to automatically highlight all expired dates in red, and any coming up within 90 days in yellow. If it contains a date which is valid or if the cell is not a date at all, to leave it unformatted.

Googled a few guides but they dont seem to work properly and my sheet also contains other than apart from dates.

Is there a way to do this?

**JeteMc** · 02-24-2016, 10:06 AM

It would be easier to say that it is or isn't possible if you'd use the "Go Advanced" button below the "Quick Reply" window and upload a sample of your spreadsheet.

**hongman** · 02-24-2016, 10:21 AM

Hi

Attached

**6StringJazzer** · 02-24-2016, 10:39 AM

See CF rules in attached. The first rule checks for the date being in the past, and the second rule checks for date being earlier than 90 days in the future.

**hongman** · 02-24-2016, 10:43 AM

That works great - how do I extend this functionality to the rest of the columns with dates?

**JeteMc** · 02-24-2016, 10:53 AM

If you look at the "Applies to:" for each rule you'll see that the ranges (columns) that are included in the rule are separated by a comma (,). To add new ranges type a comma and then the range. To expand an existing range such as $J$2:$K$66 so that it will include columns L and M change the K to an M so that it reads $J$2:$M$66

**6StringJazzer** · 02-24-2016, 11:30 AM

You can also use the Format Painter. When you copy formats, CF rules are included. That wouldn't require editing any formulas. But I thought I did apply to all columns with dates.