Excel Formula for Conditional FormattingSolved/Closed

Question

Hello,
I'm trying to find a formula and instructions for shading date values in a column on a worksheet based on the month relative to today's date. 
For example, I would like for all dates occuring in the current month or previous months to be shaded in red; all dates occuring in the the next two calendar months to be shaded in yellow and all other future dates to be shaded green. 

Thanks in advance for any help provided.

venkat1926 · Accepted Answer

in the conditional format window of cell A1 
choose under conditon 1
formula is 
and the following formula 

=OR(MONTH(A1)=MONTH(TODAY()),MONTH(A1)=MONTH(TODAY())-1)

color pattern is red 

in the second condition similarly the formula is 

=OR(MONTH(A1)=MONTH(TODAY())+1,MONTH(A1)=MONTH(TODAY())+2)
color patter is gsreen

if this is ok confirm "yes"

copy A1 down edit-pastespecial format only. 

see the logic of the formulas

Anonymous · Answer

Awesome!

Thanks so much, I do see the logic in the formulas, and I did tweak the formulas a little so that I could add a third condition.  

I have one question though, how can I make the formula apply to the year in the date as well?  For example the formula in condition 2 would apply to not only the next two months of this year, which is the desired outcome, but would also apply for all other dates in the months of october and november in previous years.

Thanks again

venkat1926 · Answer

I SUPPOSE that the same formula may work.

see the logice
=MONTH(TODAY())+1
gives 10 that is October. It does not indicate any year value.

try it 

but remember in excel versions upto 2002 there can be only 3 condition in conditional formality. I think this restriction is removed in excel 2007

Excel Formula for Conditional Formatting

3 responses

Similar discussions

Subscribe To Our Newsletter!