Combining IF + AND to Highlight Cells (Conditional Formatting)

C

Carrie2525

New Member
Joined
Jun 10, 2015
Messages
18
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
untested / unformatted

=AND(DATEDIF((B5-44),B3,”D”))>179,B4="Experienced")
 
Upvote 0
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. :(
 
Upvote 0
Carrie2525 said:
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. :(
Click to expand...
The 2 formulas are exactly the same, just the other way round, so both should give same result?What is in B4?
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0
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?
 
Upvote 0
Carrie2525 said:
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.
Click to expand...

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

Try
=and(B4="E",(B5-44)-B3>179)
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Conditional Formatting Formula
Replies
2
Views
482
awoohaw
awoohaw
P
  • Question
Conditional formatting a cell that contains both numbers and letters
Replies
1
Views
385
pbailey
P
L
  • Solved
Conditional Formatting Date Ranges
2
Replies
13
Views
629
Joe4
Joe4
P
  • Question
Highlighting a blank cell
Replies
8
Views
211
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Number format when combined with text
Replies
3
Views
88
Fluff
Fluff

Forum statistics

Threads
1,214,527
Messages
6,120,057
Members
448,940
Latest member
mdusw

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top