Conditional Formatting for passing specified dates

**Lowman** · 08-24-2009, 07:51 AM

I'm having trouble putting the correct conditional formatting on a column of dates. Any help would be greatly appreciated!!

Basically, in column 'A' I have a list of appointment dates. Column 'B' contains breach dates for a 10week target. And Column 'C' contains breach dates for an 18 week target. Looking something like this:

Appointment ---10wk breach--- 18wk breach
11/06/2009 ---16/02/2009--- 13/04/2009
01/06/2009 ---21/05/2009--- 16/07/2009
09/06/2009 ---05/02/2009--- 02/04/2009
03/06/2009 ---11/05/2009--- 06/07/2009

I need to format column 'A' so that the cells will turn Amber if the appointment was after the 10 wk breach date and turn Red if it was after the 18 wk breach date. I hope that makes sense!

I know I need to use the conditional formatting function, but am not sure of the formulas etc I need to put in!

If someone could help me out that would be fantastic!!

**NBVC** · 08-24-2009, 07:57 AM

Select the column A range of dates and go to Format|Conditional Formatting

Set Parameters: Cell Value Is >> Greater Than >> =$C2 where C2 contains first 18wk Breach Date and A2 is the top most cell in your selection...

Click Format and choose Red from the Pattern tab

Click Ok

Click Add

Set next parameters:

Cell Value Is >> Greater Than >> =$B2

Clck Format and choose Amber

Click Ok

Click Ok to end

**Lowman** · 08-24-2009, 08:52 AM

Hello, thank you very much for such a quick reply!

However, that is exactly what I had been trying, but for some reason it doesn't seem to be working??? I must be doing something wrong but I can't figure out what....

Do you think maybe the original formatting of the cells is wrong? I've tried it with both 'General' and 'Date' formatting on the Cells but had no luck!

Any other Ideas where I might have gone wrong?

**NBVC** · 08-24-2009, 08:59 AM

Try selecting column A and go to Data|Text to Columns... skip to 3rd dialogue window and then select Date from the column data format area.. next to that select DMY and click Ok...

Repeat for other date columns...

Does that fix it?

If no, attach workbook here.

**Lowman** · 08-24-2009, 09:09 AM

Woohoo! That's fixed it!

Thank you very much for your help, you have saved me a lot of time and frustration!

Cheers
Leigh

**NBVC** · 08-24-2009, 09:20 AM

Great, you are welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save