Simplifying conditional format ranges which uses EOMONTH function

[chesspupil]

I need to simplify the X and Y lines below. In the future I will need X to cover a 7 to 12 months away and Y to cover 12 to 24 months after that.



$A$1 = today()

The below formula looks at B4 end of 0(same) month and asks if $A$1+1 month is the same, or if $A$1+2 is the same, or if $A$1+3 is the same. This gives me a next month through three month conditional format .

X=OR(((EOMONTH(B4,0))=(EOMONTH($A$1,1))),((EOMONTH(B4,0))=(EOMONTH($A$1,2))),((EOMONTH(B4,0))=(EOMONTH($A$1,3))))


Y=OR(((EOMONTH(B4,0))=(EOMONTH($A$1,4))),((EOMONTH(B4,0))=(EOMONTH($A$1,5))),((EOMONTH(B4,0))=(EOMONTH($A$1,6))))

When B4 end of 0 month < $A$1+0 my conditional format gives red - expired (b4, regardless of date in the month is not expired till last day)

When B4 end of 0 month = $A$1+0 my conditional format gives ORANGE (expires end of this month)

when b4 end of 0 month > $A$1+6 my conditional format gives black and strikeout (the user has entered a date too far away) and is a form of error checking

I also need an error check if the user attempts to enter an invalid date such as 2/30/2018 or 4/31/2018

Im available via FB chat; if you like, reply here and send PM

 

[FDibbins]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

All those extra parenthesis may help you, but they can confuse things unnecessarily (for me, anyway)
=OR(((EOMONTH(B4,0))=(EOMONTH($A$1,1))),((EOMONTH(B4,0))=(EOMONTH($A$1,2))),((EOMONTH(B4,0))=(EOMONTH($A$1,3))))
This is the same, but easier to read
=OR(EOMONTH(B4,0)=EOMONTH($A$1,1),EOMONTH(B4,0)=EOMONTH($A$1,2),EOMONTH(B4,0)=EOMONTH($A$1,3))
and that lets me shorten to this...
=OR(EOMONTH(B4,0)>=EOMONTH($A$1,1),EOMONTH(B4,0)<=EOMONTH($A$1,3))
That may even help you fix the problem you hav?

 

[Tetra201]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

Try these CF rule formulas:

=OR(EOMONTH(B4,0)=EOMONTH($A$1,ROW($1:$3)))
=OR(EOMONTH(B4,0)=EOMONTH($A$1,ROW($4:$6)))

 

[chesspupil]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

All those extra parenthesis may help you, but they can confuse things unnecessarily (for me, anyway)
=OR(((EOMONTH(B4,0))=(EOMONTH($A$1,1))),((EOMONTH(B4,0))=(EOMONTH($A$1,2))),((EOMONTH(B4,0))=(EOMONTH($A$1,3))))
This is the same, but easier to read
=OR(EOMONTH(B4,0)=EOMONTH($A$1,1),EOMONTH(B4,0)=EOMONTH($A$1,2),EOMONTH(B4,0)=EOMONTH($A$1,3))
and that lets me shorten to this...
=OR(EOMONTH(B4,0)>=EOMONTH($A$1,1),EOMONTH(B4,0)<=EOMONTH($A$1,3))
That may even help you fix the problem you hav?

Thank you for your help.
This line of code has a problem:
=OR(EOMONTH(B4,0)>=EOMONTH($A$1,1),EOMONTH(B4,0)<=EOMONTH($A$1,3))

The second logical is giving a TRUE for a date in the same month or earlier than A1; and the first argument gives a true beyond the third month. I need dates outside of the original range to make the net result of the formula FALSE, while only dates inside the range to be net TRUE

 

[chesspupil]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

Thank you for giving it a shot.

Try these CF rule formulas:

=OR(EOMONTH(B4,0)=EOMONTH($A$1,ROW($1:$3)))
=OR(EOMONTH(B4,0)=EOMONTH($A$1,ROW($4:$6)))

In the running the "evaluate formula" only $A$1,1 is checked. Result is that when B4 is months 2 and 3 they are not checked as true. I do like the idea of the use of the : range though

Any thoughts?

 

[FDibbins]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

Thank you for your help.
This line of code has a problem:
=OR(EOMONTH(B4,0)>=EOMONTH($A$1,1),EOMONTH(B4,0)<=EOMONTH($A$1,3))

The second logical is giving a TRUE for a date in the same month or earlier than A1; and the first argument gives a true beyond the third month. I need dates outside of the original range to make the net result of the formula FALSE, while only dates inside the range to be net TRUE

OK, try this adjustment.....
=AND(EOMONTH(B4,0)>=EOMONTH($A$1,1),EOMONTH(B4,0)<=EOMONTH($A$1,3))

 

[chesspupil]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

That appears to have done it! Thank you so much.

Expanding into the next range, Ill let you know how that goes.

 

[FDibbins]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

That method allows you to expand you date range as needed. In fact, if you change the ,3 to a reference and put the value in it's own cell, you can adjust the range more dynamically
happy to help, keep me posted

 

[Tetra201]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

... In the running the "evaluate formula" only $A$1,1 is checked. Result is that when B4 is months 2 and 3 they are not checked as true. I do like the idea of the use of the : range though

Any thoughts?

Yes. The thoughts are: you have not tried these formulas for conditional formatting.

 

[chesspupil]

Re: Help with simplifying conditional format ranges which uses EOMONTH function

Yes. The thoughts are: you have not tried these formulas for conditional formatting.

That is true I have not, but how that impacts the true false statement generated is what I am testing it against since plugging in CFs in the menu is a
heck of a lot of extra clicking... will give it a go,

Thank you.