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

 

[gaz_chops]

e.g.

[TABLE="width: 198"]
<tbody>[TR]
[TD="width: 66"][/TD]
[TD="class: xl64, width: 66, align: right"]10/06/15[/TD]
[TD="width: 66, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, align: right"]02/02/14[/TD]
[TD]=AND(B1-B2>179,B4="Experienced")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, align: right"]15/07/15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Experienced[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Carrie2525]

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.

Yep. Gawd....so simple. That's what it was. The formula works, now. TRUE value returned, and cell highlights. I think I can make it work from here. Thank you everyone.

 

[gaz_chops]

Yep. Gawd....so simple. That's what it was. The formula works, now. TRUE value returned, and cell highlights. I think I can make it work from here. Thank you everyone.

You're very welcome.