Expiration Date in Excel Coloring

[huja]

Hi.

I'm new to this forum and english is not native language so bear with me.

I really try to figure out an easy way in excel to show me when a deadline is approaching.

So let's say I have a deadline 2022-01-31, and I type 2022-01-31 in a cell.
I want excel to turn the cell red when it reaches 2022-01-31 and keep showing red after 2022-01-31.
I also want excel to turn the cell yellow when it is 14 days left until deadline.
When it is more than 14 days left I want the cell to be green.

How do I do this in Excel? I have searched everywhere and I fail every time.

I know I can use "conditional formatting" but I do not know what formula to apply to make it work.

Thanks in advance.

 

[Toadstool]

Hi Huja,

I believe this is what you want. You can ignore columns E, F and G but because getting the formulae correct can be tricky I'm showing you how I test the formulae before putting them in a Conditional Format.

Huja.xlsx
	A	B	C	D	E	F	G
1	Date		Deadline		Red	Yellow	Green
2	31-Jan-22		31-Jan-22		TRUE	FALSE	FALSE
3	17-Jan-22				FALSE	TRUE	FALSE
4	16-Jan-22				FALSE	FALSE	TRUE
5	01-Feb-22				TRUE	FALSE	FALSE
6	30-Jan-22				FALSE	TRUE	FALSE
7	01-Jan-21				FALSE	FALSE	TRUE
8	01-Jan-23				TRUE	FALSE	FALSE
9
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=$A2>=$C$2
F2:F8	F2	=AND($A2<$C$2,$A2+14>=$C$2)
G2:G8	G2	=AND($A2<>"",$A2+14<$C$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=AND($A2<>"",$A2+14<$C$2)	text	NO
A2:A20	Expression	=AND($A2<$C$2,$A2+14>=$C$2)	text	NO
A2:A20	Expression	=$A2>=$C$2	text	NO

 

[huja]

Hi.

Thanks for the help.

Not really like this.
I wish to use the "today()" formula somehow. So when a the deadline date is closing in it will change color, based on how close to the date it is.
When today() has reached the deadline, and past deadline, it will change to red.
When today() is within 14 days (when it is 14 days left) it will change to yellow.
When today() has not reached 14 days (When it is more than 14 days left) I want it to be green.

Hope you understand what I mean.

Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.

What cells are your dates in?

 

[huja]

Hi & welcome to MrExcel.

What cells are your dates in?

Thank you.
Every deadline date starts from cell A2.

 

[Fluff]

Going down col A or across row 2?

 

[Toadstool]

2nd attempt

Huja.xlsx
	A
1	Date
2	31-Jan-22
3	15-Jan-22
4	24-Jan-22
5	25-Jan-22
6	10-Jan-22
7	01-Jan-22
8	01-Jan-23
9
10
2nd

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=$A2>TODAY()+14	text	NO
A2:A20	Expression	=AND($A2>TODAY(),$A2<=TODAY()+14)	text	NO
A2:A20	Expression	=AND($A2<>"",$A2<=TODAY())	text	NO

 

[huja]

2nd attempt

Hi.

I want exactly like this... but reverse. I tried to change it to -14 but it did not work. How can I reverse it?

 

[huja]

Going down col A or across row 2?

From A2 and down

 

[Toadstool]

Hi.

I want exactly like this... but reverse. I tried to change it to -14 but it did not work. How can I reverse it?

I don't understand what you mean by revere it.

Here's a list of dates which should cover all scenarios. Please identify the colour required for each:

Huja.xlsx
	J	K	L
1	Row 1	Date	Days from TODAY()
2	Row 2	20-Dec-21	-22
3	Row 3	27-Dec-21	-15
4	Row 4	28-Dec-21	-14
5	Row 5	29-Dec-21	-13
6	Row 6	10-Jan-22	-1
7	Row 7	11-Jan-22	0
8	Row 8	12-Jan-22	1
9	Row 9	24-Jan-22	13
10	Row 10	25-Jan-22	14
11	Row 11	26-Jan-22	15
12	Row 12	02-Feb-22	22
2nd