Conditional Format: IF formula with AND

[tennisbuck]

Hi Board,

On the attached spreadsheet, what would be the formula I can use to conditionally format cell E2 to yellow based on the following criteria:

1.) Cell C2=N
2.) Cell E2 is 305 days or greater then the hire date


Mike

Director	Hire Date	Media Fill Y/N	Date Required	60 Days	Comments
Mike	8/23/2016	N	2/21/2017	6/21/2017

<tbody>
</tbody>

 

[Weazel]

maybe something like....

=AND(C2="N",E2-B2>=305)

 

[tennisbuck]

So it doesn't turn yellow: here's the modified formula
=AND(C2=Sheet1!$A$2,E2-TODAY()=305)

Sheet 1A2 is just N

 

[Joe4]

So it doesn't turn yellow: here's the modified formula
=AND(C2=Sheet1!$A$2,E2-TODAY()=305)

Looks like you dropped/missed the greater than sign in your formula.
Take a closer look at Weazel's formula.

 

[AliGW]

Should that not be > = 350 at the end? The > is missing - maybe that's the problem?

 

[tennisbuck]

heres the formula it still doesn't work, i might be a little dense. Is there a way I can post my workbook?

=AND(C2=Sheet1!$A$2,E2-TODAY()>305)

 

[Joe4]

You cannot post workbooks, post you can post screen images, as mentioned here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Can you tell us exactly what you have in C2 and E2?
Is the value in E2 entered as a String or Date?
What does this formula return?
=ISNUMBER(E2)

 

[Weazel]

isn't the hire date in column B ?

maybe i misunderstood something

 

[tennisbuck]

Yep, the hire date is in Column B. updated formula still doesn't work. Am I using todays date right?

=AND(C3=Sheet1!$A$3,B3-TODAY()>305)

 

[AliGW]

But it should be this:

=AND(C3=Sheet1!$A$3,B3-TODAY() > = 305)

Please explain in detail what you mean by "doesn't work". Wrong result? Error message?