Two parameters for conditional formatting

[FGwynne]

Good afternoon, 

I am trying to correct some formatting on an excel sheet. I would like the formatting to change the colour of the row when the date is today or older BUT only when one of the columns is empty (ie not released). I have managed so far to get the singular column to change once displaying todays date or older. However, I cannot figure out how to apply this to the row and only apply when the separate release column is empty. I suspect I need an "AND" formula?

A simplified table to illustrate what I'm working with:

Column letter:	F	L
	5 Day Warning	Release Date
	22-May-23	21-May-23
	23-May-23
	24-May-23
	25-May-23

Current formula: =$F3<=TODAY()

Many thanks for any help :)

[#2]

@FGwynne 

Enter the following formula in the formula field:

=AND($F1<=TODAY(), $L1="")

This formula checks if the date in column F is today or older ($F1<=TODAY()) and if the release column L is empty ($L1=""). Note that the row number (1) should match the starting row of your selected range.

The conditional formatting will now change the color of the rows where the date in column F is today or older, but only if the corresponding release column L is empty. Adjust the formula and formatting options as needed for your specific requirements.

[#3]

Perfect, that's worked! Thank you.

I was also wondering if it is possible to apply a formula only when a neighbouring column is populated with a date?
For example:

H	I
Date off  (Day 0)	Expiry Date
"Date to be entered"	"=H+5"

[#4 · Accepted solution]

You can use an IF statement to check if the neighboring column contains a date, and then perform the calculation accordingly.
Assuming your "Date off (Day 0)" column is column H and your "Expiry Date" column is column I, you can use the following formula in cell I2:
=IF(ISDATE(H2), H2+5, "")
This formula uses the ISDATE function to check if the cell in column H contains a date. If it does, it adds 5 days to the date in column H (H2+5). If the cell in column H does not contain a date, the formula returns an empty string ("").
You can drag the formula down the column to apply it to the other cells in column I.
The formula will only calculate a result when the corresponding cell in column H is populated with a date.
Otherwise, it will leave the cell in column I blank.
Please adjust the column references and formula as needed based on your specific worksheet layout.

[#5]

@NikolinoDE 

Thanks again for your help. 

When I enter the above it brings back either "invalid" or "NAME?" in the logical test section. Any idea what I've done wrong?

[#6]

Please try the following modified formula to check if the neighboring cell in column H contains a date: =IF(ISNUMBER(H2), H2+5, "")
This formula uses the ISNUMBER function instead of ISDATE. In some versions of Excel, ISNUMBER can effectively check if a cell contains a valid date.

If you still encounter errors or unexpected results, please provide more details about the specific version of Excel, Operating system, storage medium, etc. you are using and the format of the data in column H (e.g., whether it contains actual date values or text representations of dates), or a file (without sensitive data) or photo/s with a step-by-step explanation.

[#7]

That worked, thanks so much for your help :)