Conditionally formatting dates with IF AND including blank cells

[EMW2159]

Hi all

I have exhausted google with this question but everything I've tried has not worked. I'd like to do the following

If the date in I1 is 30 days away and J1 is blank, flag I1 orange.
If the date in I1 is in the past and J1 is blank, flag red.

Once a date is in J1 then I need it to be white. So far I was trying this kind of thing:

=IF(AND(=I5-TODAY()<30,J5=””),0,1)

But had no luck. any help would be much appreciated thanks

 

[Peter_SSs]

Something like this?

21 06 17.xlsm
	I	J
1	20-May-21
2	25-May-21
3	30-May-21	5/06/2021
4	04-Jun-21
5	09-Jun-21	12/06/2021
6	14-Jun-21
7	19-Jun-21
8	24-Jun-21
9	29-Jun-21	30/06/2021
10	04-Jul-21
11
12	14-Jul-21
13	19-Aug-21
CF Dates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I1:I39	Expression	=AND(I1<TODAY(),J1="",I1<>"")	text	NO
I1:I39	Expression	=AND(I1-TODAY()<30,I1>=TODAY(),J1="")	text	NO

 

[EMW2159]

This is perfect - thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.