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
808
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
3
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
1
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
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
1