Simplifying conditional format ranges which uses EOMONTH function

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
 
Last edited:
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.
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Re: Help with simplifying conditional format ranges which uses EOMONTH function

Maybe with =not(iserror(formula))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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