chesspupil
Board Regular
- Joined
- May 21, 2006
- Messages
- 52
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
$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
Last edited: