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

 

[Carrie2525]

Gaz, I'm sorry -- it used to say "Experienced" but I changed a few values to see if I could get your formula to work. (To clarify, your formula "took" -- it did not return an error message to me, but none of the cells would highlight, as I specified. I thought maybe if I changed the values, I could make it work.)

Using this formula you just posted, the same thing happened -- Excel accepted the rule in conditional formatting, but no highlights appeared.

 

[BGY23]

Hi Carrie,

=AND(B2-B3>179,B4="Experienced") Worked for me. I tested it first.

Open Conditional format and look at the formual. Conditional formating "sometimes" adds extra " to be "helpful". Check the formual looks like above. Don't forget to set the fill colour too.

Make sure your dates in the two cells are NOT Text but are in fact date formatted. In an adjacent cell type =istext(b2) If you get True the formual won't work until you change them to Numbers and format them as Date.

 

[BGY23]

Carrie,

Honestly I do know how tom spell formula, just one finger is ever so slightly quicker than the other LOL

 

[gaz_chops]

Carrie,
Put this formula in a cell =(B5-44)-B3
what result do you get?

 

[gaz_chops]

Just a thought! You are applying this to the "Range" that you want to highlight?

 

[Carrie2525]

BGY, thank you for continuing to help me. So, instead of going right to conditional formatting, I pasted your formula in the spreadsheet, and I may have found a problem. No matter what I do, the equation always returns FALSE. That would mean the cells would never highlight, correct? Where did I go wrong with this data? It seems like the conditions should make this a TRUE situation. I'll try and post a screenshot.

https://www.dropbox.com/s/uxqf7sqckj3bmuv/Screenshot Excel.png?dl=0

 

[Carrie2525]

Gaz, I believe so. I select the range, and then I select Conditional Formatting. That applies it to the entire selection, correct?

 

[gaz_chops]

Gaz, I believe so. I select the range, and then I select Conditional Formatting. That applies it to the entire selection, correct?

Yes.

 

[Carrie2525]

Carrie,
Put this formula in a cell =(B5-44)-B3
what result do you get?

Gaz, I ammended my cells to match BGY's formula. He kept using B2, so I moved B5's value up there. Please see link to screenshot above. Do you still want me to try a cell formula?

 

[gaz_chops]

In your screenshot you are saying 2/2/14 - 15/7/15 resulting in a negative!

Think it should be either B1 or B3 minus B2.