AND Formula, Conditional Formatting, Multiple Criteria

mattyblueice

Board Regular
Joined
Jul 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. MacOS
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:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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:
Upvote 0
Also, if B2 is 0 & J30 contains a date then this part
Code:
J30< b2
cannot be true
 
Upvote 0
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:
Upvote 0
Try removing it from the formula.
 
Upvote 0
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! ;)
 
Upvote 0
Glad you got it sorted & thanks for the feedback
 
Upvote 0
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! :)
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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