Excel 2003 conditional formatting on future deadlines

**Racha3146** · 10-22-2012, 09:53 AM

Hi all,

First of all, I apologise if this question has been answered before; if it has I haven't been able to find it.

My query is this - I have a group of deadlines for courses done by employees and I need to highlight a) when they are overdue, b) when they have 2 months to go before the deadline and c) when they have renewed the course. Overdue will be red, 2 months will be orange and renewal will be green.

There are different courses with different deadlines. Most of the courses need renewing annually so once a course has been done, I need to highlight it in green. Two of the courses are renewed every 3 years but the same formatting will apply (i.e. colours).

If someone could help, I would be very grateful as I have searched all over for an answer. I am not an Excel wizard but I know my way around.

Cheers

Racha3146

**ChemistB** · 10-22-2012, 10:04 AM

Do you have the renewal date in a specific column? Or is there a date of subscription or enrollment and it would be x years after that?

**Racha3146** · 10-22-2012, 10:10 AM

No I don't. I just have the dates the course was completed.

**ChemistB** · 10-22-2012, 11:07 AM

Okay, so the actual renewal date is in your head but is x years after the completion date. Yes? I'm not clear on how you would know they renewed? It there a cell where you'd mark that they have renewed?

Let's do an example with 3 years. Let's say your data is in Row 2 and your completion date is in col F. There's an "X" in Col G if they renewed

Select A2:H2 (however far out your data goes) In conditional formatting> Use Formula
=AND($G2<>"X", $F2+DATE(3,1,0)>= TODAY())
Format Red
= AND($G2<>"X", $F2+DATE(3,3,0)>=TODAY())
Format Orange
=$G2="X"
Format Green
Does that work for you?
This would be much easier if you could upload an example of how your data is set up (Go Advanced> Manage Attachments)