due Dates and Conditional Formating

[inazone230]

Hello, Im new to the group and Excel, Unfortunatley my work is still using 2003 and Im stuggling to get my head round conditional formating. So hopefully you folks can assist me. Many thanks inadvance.

What I have at the moment is a spreadsheet similiar to :

A B C
Task Date Due enter Date of completion.
2
3
( to sometime several hundred
varies every month.)

What I want to do is on date due change the colour to yellow, 8 days before; to red 4 days before and if uncompleted (as i believe i can only have 3 rules? ). And finally green when completed and date entered.

I think i have the yellow and red steps sorted by using
Rule1 =(B2-TODAY())<8 {YELLOW}, 8 days till due
rule 2 =(B2-TODAY())<4 {RED}. 4 days till due

Am I right in thinking these need to be sequential?

However I am struggling to get the colour to change to green when completed the task. Any help would be great.

Sorry could I ask one more thing; can I paste this format to the whole column B so new entries will automatically have this format?

Thanks

[kbkumar]

Hi - Welcome to the forum. would be helpful if you could upload a workbook with sample data.

This will help members to provide solutions faster.

To upload a workbook, click "Go Advanced" button below and click on the attachment option.

[inazone230]

Thanks for the quick response.

Sorry Im not at work but have tried to make a small version of what we use I hope this makes sense.

Practice Workbook.xls

[jlax34]

2 things:

First off, for the yellow color which is for something that is due between 5 and 8 days, I would use the "AND" formula. In the example above its

Rule1 =AND((B2-TODAY())<8, (B2-TODAY())>4)

2nd, I assume for the "Date completed" is going to be blank until completed? If so, then all you need to do is have your FIRST condition for COLUMN B is "C2<>0" {green}. That will change to green anything that has a value in column C

...also be glad you are using conditional formatting in excel 2003. It is one of the few things that I find much more difficult to use in 2010 personally...

[inazone230]

That works wonders, Thank you very much for your assistance.

I really need to practice formulae again.. I cant remember a thing with these.