Combining IF + AND to Highlight Cells (Conditional Formatting)

[Carrie2525]

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

 

[mole999]

untested / unformatted

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

 

[gaz_chops]

Try

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

 

[Carrie2525]

Mole, yours gave me an error message from Excel.

 

[Carrie2525]

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_chops]

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?

 

[Carrie2525]

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.

 

[BGY23]

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")

 

[Carrie2525]

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?

 

[gaz_chops]

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)