Conditional Formatting | Color Cells Within Certain Dates

[urban_player]

Hi All,

I am trying to accomplish the following:

1) If column B contains text and the date entered in column E is within 1 week from today's date, highlight cell in column B as colour
2) If column B contains text and the date entered in column E is within 1 month from today's date, highlight cell in column b as colour

Column B contains a ticket reference
Column E contains dates for when the ticket is logged

Any assistance is greatly appreciated.

Many thanks!

urban

 

[Yongle]

Select column B \ click on Conditional formatting \ add new rule with formula per H1
Select column B \ click on Conditional formatting \ add new rule with formula per J1
I usually test the formula in the worksheet first (as below in columns G & I) and then delete them

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	a	Ticket Ref	c	d	date logged	e	FALSE​	=AND(E1>=TODAY(),E1<=TODAY()+7,B1<>"")	FALSE​	=AND(E1>TODAY()+7,E1<=TODAY()+30,B1<>"")
2​		ref001			13/04/2019​		TRUE​		FALSE​
3​		ref002			15/04/2019​		TRUE​		FALSE​
4​		ref003			17/04/2019​		FALSE​		TRUE​
5​		ref004			24/04/2019​		FALSE​		TRUE​
6​		ref005			27/04/2019​		FALSE​		TRUE​
7​		ref006			30/04/2019​		FALSE​		TRUE​
8​		ref007			03/05/2019​		FALSE​		TRUE​
9​		ref008			06/05/2019​		FALSE​		TRUE​
10​		ref009			09/05/2019​		FALSE​		TRUE​
11​		ref010			12/05/2019​		FALSE​		FALSE​

Sheet: Sheet1

 

[urban_player]

Hola Yongle,

Thank you for your post.

I created two conditional formatting rules.
One rule contains =AND(E7>=TODAY(),E7<=TODAY()+7,B7<>"")
The other: =AND(E7>TODAY()+7,E7<=TODAY()+30,B7<>"")

When i apply these to cells A7:A3000
Nothing works

 

[Yongle]

I do not know what you are doing wrong but this works

- select B7 to B3000 NOTE cell B7 must be the first cell in selected range
- add new CF rule
- select use formula to determine
- formula in H7 pasted into Format values where this is true
- added red colour fill format
- added another rule for formula in J7
- added green colour fill format

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
4​
5​
6​	a	Ticket Ref	c	d	date logged	e
7​		ref001			11/04/2019​		TRUE​	=AND(E7>=TODAY(),E7<=TODAY()+7,B7<>"")	FALSE​	=AND(E7>TODAY()+7,E7<=TODAY()+30,B7<>"")
8​		ref002			12/04/2019​		TRUE​		FALSE​
9​		ref003			13/04/2019​		TRUE​		FALSE​
10​		ref004			29/04/2019​		FALSE​		TRUE​
11​		ref005			01/05/2019​		FALSE​		TRUE​
12​		ref006			03/05/2019​		FALSE​		TRUE​
13​		ref007			05/05/2019​		FALSE​		TRUE​
14​		ref008			07/05/2019​		FALSE​		TRUE​
15​		ref009			09/05/2019​		FALSE​		TRUE​
16​		ref010			11/05/2019​		FALSE​		FALSE​

Sheet: Sheet1

NOTE
I assume you meant B7:B300 this is what you said originally
highlight cell in column B as colour