Conditional format formula

[Sherlock794]

I have a report that lists stock orders and a date required column. I would like to add a condition format that reads looks at the date required date, minus today. End state I would like to apply the condition format to the corresponding stock code, if it is due in more that 14 days (green), between 14 and 0 days (amber) and if it is passed the date (0) required then (red).

 

[Toadstool]

Hi Sherlock794,

Try this:

Sherlock794.xlsx
	A	B	C
1
2
3		Stock Code	Date Required
4		XYZ001	30-Jun-21
5		XYZ002	12-Jul-21
6		XYZ003	24-Jul-21
7		XYZ004	05-Aug-21
8		XYZ005	17-Aug-21
9		XYZ006	29-Aug-21
10		XYZ007	10-Sep-21
11		XYZ008	12-Jul-21
12		XYZ009	25-Jul-21
13		XYZ010	03-Aug-21
14		XYZ011	12-Aug-21
15
16
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B9999	Expression	=AND(C4-TODAY()<0,C4<>"")	text	NO
B4:B9999	Expression	=AND(C4-TODAY()>=0,C4-TODAY()<=14)	text	NO
B4:B9999	Expression	=C4-TODAY()>14	text	NO

 

[Sherlock794]

Toadstool, this is just what I am looking to achieve. I will give it ago tomorrow, thank you.

 

[Toadstool]

Toadstool, this is just what I am looking to achieve. I will give it ago tomorrow, thank you.

You're welcome!

...and I like the way you marked your own post as the solution

 

[Sherlock794]

I have confidence in you

 

[dpbarry]

Hi Sherlock794,

Try this:

Sherlock794.xlsx
	A	B	C
1
2
3		Stock Code	Date Required
4		XYZ001	30-Jun-21
5		XYZ002	12-Jul-21
6		XYZ003	24-Jul-21
7		XYZ004	05-Aug-21
8		XYZ005	17-Aug-21
9		XYZ006	29-Aug-21
10		XYZ007	10-Sep-21
11		XYZ008	12-Jul-21
12		XYZ009	25-Jul-21
13		XYZ010	03-Aug-21
14		XYZ011	12-Aug-21
15
16
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B9999	Expression	=AND(C4-TODAY()<0,C4<>"")	text	NO
B4:B9999	Expression	=AND(C4-TODAY()>=0,C4-TODAY()<=14)	text	NO
B4:B9999	Expression	=C4-TODAY()>14	text	NO

Hi Toadstool

Your solution is very close to what I am also looking but instead of days, how would you modify the expression to calculate based on month?

Regards

Declan

 

[Toadstool]

Hi Declan,

You don't say exactly how you want to highlight the age so I'm using today to 3 months ago is green, over 3 months but less than 6 months ago is amber and over 6 months but less than 12 months is red.

DPBarry.xlsx
	B	C
3	Stock Code	Date Required
4	XYZ001	09-Sep-20
5	XYZ002	20-Dec-20
6	XYZ003	23-Jan-21
7	XYZ004	26-Feb-21
8	XYZ005	05-May-21
9	XYZ006	08-Jun-21
10	XYZ007	10-Jul-21
11	XYZ008	13-Aug-21
12	XYZ009	16-Sep-21
13	XYZ010	10-Oct-21
14	XYZ011	13-Nov-21
15		17-Dec-21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B18	Expression	=AND(EDATE(C4,6)<TODAY(),EDATE(C4,12)>=TODAY())	text	NO
B4:B18	Expression	=AND(EDATE(C4,3)<TODAY(),EDATE(C4,6)>=TODAY())	text	NO
B4:B18	Expression	=AND(EDATE(C4,0)<TODAY(),EDATE(C4,3)>=TODAY())	text	NO

 

[dpbarry]

Hi Declan,

You don't say exactly how you want to highlight the age so I'm using today to 3 months ago is green, over 3 months but less than 6 months ago is amber and over 6 months but less than 12 months is red.

DPBarry.xlsx
	B	C
3	Stock Code	Date Required
4	XYZ001	09-Sep-20
5	XYZ002	20-Dec-20
6	XYZ003	23-Jan-21
7	XYZ004	26-Feb-21
8	XYZ005	05-May-21
9	XYZ006	08-Jun-21
10	XYZ007	10-Jul-21
11	XYZ008	13-Aug-21
12	XYZ009	16-Sep-21
13	XYZ010	10-Oct-21
14	XYZ011	13-Nov-21
15		17-Dec-21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B18	Expression	=AND(EDATE(C4,6)<TODAY(),EDATE(C4,12)>=TODAY())	text	NO
B4:B18	Expression	=AND(EDATE(C4,3)<TODAY(),EDATE(C4,6)>=TODAY())	text	NO
B4:B18	Expression	=AND(EDATE(C4,0)<TODAY(),EDATE(C4,3)>=TODAY())	text	NO

Cheers Toadstool

Works perfect

Regards

Declan

 

[Toadstool]

Cheers Toadstool

Works perfect

Regards

Declan

You're welcome.