Conditional formatting formula with IF, AND

Question

I would like to create a formula using conditional formatting along a check box option, for example

• Column G is an admitting date
• Column H has a formula that gives you the date of follow up, which is six days after the admitting date  (=G5+6). Column H also has a conditional formatting formula, if the dates in column H cells are less than today’s date, then the cell will be highlighted in red.
• I have added check boxes to column, this box will be checked if and when the employee has followed up with the client.

The formula that I am looking for is:  If date in column H is less than today’s date And check box in column I is not checked then highlight dated cell in column H. Or if date in column H is less than today’s date and check box in column I is checked then do not highlight dated cell in column H.

Can you please help accomplish this formula?

Answer 1

Hi Liz_VC,

Based on your description, you would like to add conditional formatting in the Excel workbook. When dates in Column H is less than today, highlighting them, otherwise making checkboxes in Column I checked. I would like to follow the steps below to add conditional formatting:

    1. Go to Home> Styles> Conditional Formatting, click New Rule.

    2. Create a new rule as follows.

This article about using a formula to apply conditional formatting is for your reference.

    3. In this case, you can mark checkboxes whose following dates are not highlighted. You can refer to this article to add check boxes in Excel.

Regards,

Yoga

Answer 2

Hi,

Try this

1. While on cell I2, go to Developer > Controls > Insert > Form Control > Check Box (Form Control)
2. Draw the check box in cell I2
3. Right click on the check box and go to Format Control
4. In the cell Link box, select cell J2

When you check the box, TRUE will appear in cell J2, else FALSE

5. Click on cell H2 and write the following formula in the Conditional formatting dialog box

=AND($H2<TODAY(),$J2=FALSE)

6. Select the Format of your choice
7. Click on OK/Apply

Hope this helps.

Answer 3

Hi Liz_VC,

Do you have any updates?

Regards,

Yoga

Answer 4

Hello Yoga,

I apologize for the late response, the information that you provided was very helpful. I did include the check boxes and linked them an adjacent cell that gave true or false results, once I did that I used the formula below in the conditional formatting section

=IF(K5<=TODAY(), AND(M5=FALSE)) then highlight in red .  The only issue I had was having to link each check box to an individual adjacent cell for the entire spreadsheet was a bit time consuming.

The spreadsheet looks great and is in the testing stages.

Thank You once again for your help  

Liz

Answer 5

Ashish,

Your response was very helpful, I initially used an IF  formula for the conditional formatting and kept it, but your AND formula is very helpful and shorter.

Once again, thank you

Liz