Another Date Conditional Format

[pjrweb]

Evening,

I am struggling to find this type of formatting.

I enter a date when a person took a test e.g. 29/AUG/11

If 275 days i want it to go Amber and in 365 days go red.

Any help on the code?

Regards,
PJ

 

[pedro-egoli]

Thanks for that.
It worked ok so hope it does for original poster .

Pedro

 

[pjrweb]

Still will not work.

Column B from B2 downwards contains date of when tests where taken.

Colours all in the column in one colour and not another.

 

[pedro-egoli]

What cells have dates in them and is there a formula in each cell or just the date manually entered.

Pedro

 

[pjrweb]

All rows in column B except B1 has a manually entered date.

 

[pedro-egoli]

Try this.

In cell B2 go to conditional formatting and set conditions as

1. Formula is =(B2="")
Set Pattern as "no colour", font as "automatic"

2. Formula is =(B2=TODAY()-365)
Set Pattern as Red

3.Formula is =(B2=TODAY()-275)
Set Pattern as amber
Once CF is finished click OK and return to cell B2.
Right click and copy
Then highlight all the cells you wish the CF format to be used in
Right click and go Paste Special/Format

That does the trick on my test sheet.
The only thing to occur though is that one day after the CF is triggered the cell will no longer be coloured amber in the first instance ,

What action do you propose to do once cell turns amber?

Pedro

 

[pedro-egoli]

Had another go at this and added a column "no of days since test"
Here is result

Excel Workbook
	B	C
1	Test Date	days since test
2	30-11-10	276
3	31-08-10	367
4	01-12-10	275
5	01-09-10	366
6	05-10-10	332
7	02-09-10	365
8	03-12-10	273
Sheet1

Conditional formatting done in each column as follows
B2
Condition 1 =(B2<=274)
No format
Condition 2 =and(c2>274,c2<365)
Colour Amber
Condition 3 = C2>364

C2
Condition 1 Cell Value is between 275 and 364
Colour Amber
Condition 2 Formula is =C2>=365

That should produce more meaningful figures too

Pedro