# Combining IF + AND to Highlight Cells (Conditional Formatting)

#### Carrie2525

##### New Member
Hello! I am using a formula to calculate the number of days between two dates, and then highlighting cells based on that number:

=(DATEDIF((B5-44),B3,”D”))>179 -- if the number of days between two dates is greater than 179, then highlight a cell.

This formula is working great for me, but I have come to an area of the spreadsheet where I need to add a second variable into the mix. Basically, I want something that combines the formula above with something like

AND B4="Experienced"

and then use the new formula to determine whether or not to highlight a cell. (i.e. If the number of days between two dates is greater than 179, AND B4="Experienced", then highlight a cell.)

Does that make sense? Can anyone help? Excel keeps finding different ways to tell me that my new formula is garbage. Thank you

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
untested / unformatted

=AND(DATEDIF((B5-44),B3,”D”))>179,B4="Experienced")

Try

=and(B4="Experienced",(DATEDIF((B5-44),B3,”D”))>179)

Mole, yours gave me an error message from Excel.

Gaz, no error message with yours, but it did not highlight any cells. I tried adjusted a few variables, too. (Changing 179 to 1, changing "Experienced" to "E".) No dice.

Gaz, no error message with yours, but it did not highlight any cells. I tried adjusted a few variables, too. (Changing 179 to 1, changing "Experienced" to "E".) No dice.
The 2 formulas are exactly the same, just the other way round, so both should give same result?What is in B4?

In B4, there is a capital letter E.

I don't have to use the DATEDIF formula -- it's just the only way I knew how to do the first rule for highlighting. I'm always open to another suggestion, if anyone has something.

Hi Carrie,

As you're interested in days then Try this formual in the cell.

=IF(AND(B2-B3>179,B4="Experienced"),"ok","no")

Once yo get it work adapt it for Conditional Formatting

=AND(B2-B3>179,B4="Experienced")

BGY, I used your first formula, and "ok" populated. Using your second formula as the conditional formatting rule, I keep getting the standard "We found a problem with this formula..." message. What on earth am I doing wrong, you guys?

In B4, there is a capital letter E.

I don't have to use the DATEDIF formula -- it's just the only way I knew how to do the first rule for highlighting. I'm always open to another suggestion, if anyone has something.

If it is a capital E, why check if it says "Experienced"?

Try
=and(B4="E",(B5-44)-B3>179)

Replies
3
Views
40
Replies
5
Views
147
Replies
4
Views
349
Replies
12
Views
280
Replies
5
Views
122

1,203,640
Messages
6,056,493
Members
444,870
Latest member
xuanhoi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back