Help with conditional formatting for a date.

Question

I have a sheet with a a cell (G2) that contains due date in form DD/MM/YYYY, I want to highlight this cell based on two conditions, G2 being less that 5 days away (or the past) and L2 not containing any text. Is this possible.

Answer 1

Hi Dave.

Here is the procedure to add the Conditional Formatting you want.

1. Select all of the cells that you want to format. I used G2:G15 in the screenshot below. You can also include additional columns if desired.
2. On the Home ribbon in the Styles section, select Conditional Formatting>New Rule>Use a Formula.
3. In the Format Values line enter this formula.

=AND($G2-5<TODAY(),$L2="")

4. Click on the format button at the lower right of the dialogue window and set the formatting you want from the Format Cells dialogue. Then press OK and OK.

Answer 2

Here is the formula to add that condition. However, I wasn't sure whether it should be formatted if H2 is "No" or shouldn't be. Check this with cells formatted if H2 is "No".

=AND($G2-5<TODAY(),H2="No",$L2="")

If that is backwards and they should not be formatted if H2 is "No" then use this.

=AND($G2-5<TODAY(),H2<>"No",$L2="")

Answer 3

Amazing thank you, Sorry, and if i also wanted to add a condition that cell h2 should = "No" ?

Answer 4

Just a note. I got the first formula backwards so I edited the post. Make sure you are looking at the current one.