Conditional formatting in Excel for blank values & dates

[David77]

Hello everybody,

To make this quick, i have the following setup.

I want to create a conditional formatting formula so whenever I have a value in column M (basically whenever the corresponding cell in column M is not blank), then my corresponding cell in column G should turn yellow.

How do I do this? Does anybody know?

Furthermore,

I have the following setup on the right side in my excel sheet:

Is it possible to create a conditional formatting so once the order date has been entered into Excel in column L and we have passed that date + 4 additional days, the designated cell value in column L gets highlighted with a red color?

So for example, if cell L1 has order date 16-10-2020 and we are on the 20-10-2020 now (4 days later) and the value in cell M1 has not been removed (so it still has the long number 8000166464/8000216970), then cell L1 will be highlighted with a red color until the value from cell M1 is removed and that cell goes blank.

I hope this makes sense!

I would really appreciate some help here, I cannot explain how much this would mean to me, it would truly make my day! Thank you so very much everybody

PLEASE let me know if you want more photos or information as well! I will be happy to respond ASAP!

Kind regards,
David

 

[Fluff]

For column G you can use

=M2<>""

and for L

=AND(L2<=TODAY()-4,M2<>"")

For future reference you would do better to post data to the board using the XL2BB add-in, rather than an image.

 

[David77]

For column G you can use

=M2<>""

and for L

=AND(L2<=TODAY()-4,M2<>"")

For future reference you would do better to post data to the board using the XL2BB add-in, rather than an image.

Hello Fluff,

Thank you so much for the extremely fast response! You're always there for me

I tried your formula, but it doesn't seem to work?

Cell G1 is still red, not yellow

 

[AlanY]

your 2 conditions in Cols A &B

Book1
	A	B	C
1		123	23/10/2020
2			21/10/2020
3		456	19/10/2020
4			17/10/2020
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=AND(TODAY()-C1,NOT(ISBLANK(B1)))	text	NO
A:A	Expression	=NOT(ISBLANK(B1))	text	NO

 

[Fluff]

You need to select new rule & use a formula.

 

[Fluff]

Note ISBLANK will fail if the cell contains a formula & (IMO) is therefore best avoided.

 

[David77]

your 2 conditions in Cols A &B

Book1
	A	B	C
1		123	23/10/2020
2			21/10/2020
3		456	19/10/2020
4			17/10/2020
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=AND(TODAY()-C1,NOT(ISBLANK(B1)))	text	NO
A:A	Expression	=NOT(ISBLANK(B1))	text	NO

hello thank you!

the formula works, but the one for the date

 

[David77]

hel

You need to select new rule & use a formula.

hello Fluff,

I used Alan's formula for the yellow highlight, but i still need the date highlight if it's been 4 days since the good was received.

Can you help?

 

[David77]

your 2 conditions in Cols A &B

Book1
	A	B	C
1		123	23/10/2020
2			21/10/2020
3		456	19/10/2020
4			17/10/2020
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=AND(TODAY()-C1,NOT(ISBLANK(B1)))	text	NO
A:A	Expression	=NOT(ISBLANK(B1))	text	NO

If I type in "4" in cell O2 (4 days) and type in 24-10-2020 in cell L2 (so it's been received today), it still highlights it with a red color. It shouldnt light cell O2 with a red color before it's been 4 days. So if the current date is 24-10-2020, the cell L2 shouldnt be red before we reach the date of 28-10-2020 because it's 24-10-2020 + 4 from cell 02 = 28-10-2020.

Does that make sense? Can you help me with this please? Thank you!

 

[David77]

By the way, if you want more photos or information please let me know guys!