# Excel Formula for Conditional Formatting

Solved/Closed
Scott - Sep 28, 2009 at 01:53 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 29, 2009 at 08:32 PM
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.

## 3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 28, 2009 at 09:16 PM
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
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 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 29, 2009 at 08:32 PM
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