Excel Formula for Conditional Formatting [Solved/Closed]

Scott - Sep 28, 2009 at 01:53 PM - Latest reply: venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen
- 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.
See more 

3 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 28, 2009 at 09:16 PM
3
Thank you
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

Thank you, venkat1926 3

Something to say? Add comment

CCM has helped 1705 users this month

1
Thank you
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 29, 2009 at 08:32 PM
1
Thank you
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