# Conditional Formatting

daddy3108 19:03 06 Feb 2011
Locked

Hi

VoG II 19:11 06 Feb 2011

Hi. Do you have a question?

daddy3108 19:15 06 Feb 2011

Yes, please.

I am new to using Excel 2007 and am struggling with the new setup.

I have a start date and need to add 15 workdays which I can write a formula for, but the problem is the conditional formatting as I need to achive the following:

the cell needs to change colour through the process as follows 1-4 days needs to be clear, then 5-8 needs to be green, 9-12 can be amber and 13-15 needs to be red.

Can you help?

VoG II 19:23 06 Feb 2011

Assuming A1 (I know that won't be correct but adjust to suit.

You will need to use the Use a formula option then

Green =AND(A1>=5,A1<=8)

Amber =AND(A1>=9,A1<=12)

Red =AND(A1>=13,A1<=15)

daddy3108 19:33 06 Feb 2011

So will I need to create a new rule ??

What I need is one complete formula

What I need is example: - cell A1 to have a start date (today’s date), cell B2 to have a end date (of 15 working days later). But one combined formula that will change cell B2 's colour according to my previous mail ...

Hope this make sense, late Sunday evening working

VoG II 20:10 06 Feb 2011

You need 3 separate formulas. Select the cell. On the Home tab click Conditional Formatting, New Rule, Use a formula, enter the first formula, select the formatting then click OK.

Click Conditional Formatting again, select Manage Rules, click New Rule, enter the second formula, and so on...

daddy3108 20:13 06 Feb 2011

Ok ...... understand, will try later.

Many Thanks

daddy3108 20:53 06 Feb 2011

I have tried this and it is still not working. What am I doing wrong?

I have the start date in A1 and the end date in B1. B1 has the formula =WORKDAY(A1,15,0) so I know the exact end date in 15 days time.

I have then gone to conditional formatting, new rule and pasted in your formulas as instructed, but nothing is happening?

Please help.

VoG II 21:01 06 Feb 2011

You need to change A1 in the formulas that I posted to B1.

daddy3108 21:07 06 Feb 2011

Have treid that =AND(B1>=5,B1<=8) and it still doesn't work .....

daddy3108 20:04 07 Feb 2011

VOG

Came up with the following which seems to work, but it seems like a lot of work/effort... any hints on how to shorten/reduce the number of cell usage.

Cell A1 - start date
Cell B1 =TODAY()
Cell C1 =NETWORKDAYS(A1,B1)
Cell D1 =WORKDAY (A1,15,0)

Conditional formating in cell D1
=AND(IF(C1>=5,C1<=8)) Green
=AND(IF(C1>=9,C1<=12)) Amber
=AND(IF(C1>=13,C1<=15))Red

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone