AND Formula, Conditional Formatting, Multiple Criteria

[mattyblueice]

HI am building a dashboard where I want to highlight the monthly values for when the selected customer was part of a program. I have multiple years with monthly data, current and then some future months. The criteria I want conditional formatting to highlight yellow or TRUE in each monthly cell is:

* Current month stated as, example, 1/1/2019, is greater than or equal to the effective date
* Removal date is 0 meaning they are still on the program
* Selected month data is not blank meaning there is no data yet

B1: Effective Date - value is 7/1/2017
B2: Removal Date - value is 0
J30: Current Month (1/1/2018)
K30: Data (no data so value is 0)

My formula is giving me false, when the conditions are all true
=AND(J30>=B1, J30< b2, b2="0," k30<="">0)

Any idea on what I am doing wrong here?

Thanks!

 

[Joe4]

My formula is giving me false, when the conditions are all true
=AND(J30>=B1, J30< b2, b2="0," k30<="">0)

Your last two conditions look pretty messed up. I think you have some double-quotes in the wrong places.
I think the third one should be:
b2=0
(notice no double-quotes in there anywhere)

I have no idea what you are trying to do in that last condition. Can you explain what you are trying to accomplish with that formula. It doesn't seem to make sense, perhaps it is getting mangled (see my comment at the bottom, if that is what is happening).

[COLOR=#333333]" k30<="">0[/COLOR]

Note: If your formulas are being altered, it is probably because your > and < signs are being interpretted as HTML code. In order to avoid that, put spaces on both sides of every > and < sign.

 

[Fluff]

Also, if B2 is 0 & J30 contains a date then this part

J30< b2

cannot be true

 

[mattyblueice]

Hey Joe - I got a weird result when I tried putting in my formula, here is how my formula is written:
[FONT=&quot]AND(J30> =B1, J30< B2, B2=0, k30< >0[/FONT]

<style> <!-- /* Font Definitions */ @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @Page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style><b2, b2="0," k30="" <="">
So J30 does contain a date and B2 - the cell is formatted as a date, but since the removal date hasn't occurred yet it's 0 or 1/01/1900. This is probably causing the formula to be false. Do you have any ideas to get around that?
</b2,>

<style> <!-- /* Font Definitions */ @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @Page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>

 

[Fluff]

Try removing it from the formula.

 

[mattyblueice]

Ok I got it to work by adding a OR

OR(AND(J30> B1, B2=0,K30=0),AND(j30 <B2)

Thanks everybody - these formulas can kill your brain!

 

[Fluff]

Glad you got it sorted & thanks for the feedback

 

[Joe4]

Glad you got it working, though I noticed it still looks like you are having problems posting your formula here.
Here is a further description of what I was referring to: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

<a2[ code]
</a2[>

 

[mattyblueice]

Thanks Joe, I think I went wrong by not putting a space in both front and behind. I appreciate your help - time for a beer!

 

[Joe4]

I think I went wrong by not putting a space in both front and behind.

You got it!

I appreciate your help - time for a beer!

Unfortunately, not for me. They have some crazy policy about not drinking at work!