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

 

[chesspupil]

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

Now how do I institute error checking such a person on 10/30/2017 performs an update on the very last day. the data entry person updates to 2/30/2018... the conditional format doesn't do anything the formulas all go #VALUE ! THE CF responds white.

 

[FDibbins]

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

Maybe with =not(iserror(formula))

 

[chesspupil]

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

If you PM where, I can send you a sample of my sheet in its final form, minus updated data.