AND Formula, Conditional Formatting, Multiple Criteria

mattyblueice

Board Regular
Joined
Jul 24, 2014
Messages
74
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!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
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).
Code:
[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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Also, if B2 is 0 & J30 contains a date then this part
Code:
J30< b2
cannot be true
 

mattyblueice

Board Regular
Joined
Jul 24, 2014
Messages
74
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:"MS 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-format:eek:ther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"MS 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-format:eek:ther; 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:"MS 明朝"; 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:"MS 明朝"; 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:"MS 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-format:eek:ther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"MS 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-format:eek:ther; 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:"MS 明朝"; 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:"MS 明朝"; 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>
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try removing it from the formula.
 

mattyblueice

Board Regular
Joined
Jul 24, 2014
Messages
74
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you got it sorted & thanks for the feedback
 

mattyblueice

Board Regular
Joined
Jul 24, 2014
Messages
74
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
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!:mad:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,632
Messages
5,523,994
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top