Meeting 12 formatted conditions

[Loadmaster]

Cells C2:C13 has JAN - DEC. If todays month is between the 1 - 31 of Jan I
require the previous month “DEC†conditionally formatted with a color that I
specify from the color pallet. If todays month is between the 1-28 or 29th of
Feb I require the previous cell month “JAN†also conditionally color
formatted. As conditional formatting only allows you to set a maximum of
three conditions, how do I format all the months in C2:C13 the same way?

 

[Gord Dibben]

You say between Jan 1 and Jan 31

What do you want if the date is Jan 1 or Jan 31?


Gord Dibben MS Excel MVP

 

[Loadmaster]

Sorry about that, I wanted "DEC" for both those dates too. What are the hours
of operation at MS Office for answering questions. I sent this msg a minute
ago but I think it timed out when I pushed the post button.

 

[John C]

If you want the highlighted cell to be the same color, no matter what, the
conditional formatting you would type in to C2 would be as follows:

=C2=TEXT(DATE(YEAR(TheDate),MONTH(TheDate)-1,1),"mmm")

Highlight cells C2 through C12, and go to Format|Conditional Formatting, and
press OK. This will copy all the conditional formatting for all the select.

Obviously, TheDate referenced above will be the date that you are basing
what you want highlighted.

A second choice, if you want DIFFERENT colors for each month if they meet
the criteria, to do this, first, format the cell(s) as if their condition was
met, then, the formula that would go in cell C2 would be the following:
=C2<>TEXT(DATE(YEAR(MyDate),MONTH(MyDate)-1,1),"mmm")
And the formatting would be to clear out any colors you have their, leaving
ONLY the one that the criteria of the month before with it's "normal" state.

 

[Loadmaster]

It didn't work, I am trying to understand exactly what you mean in the second
choice. How do I format the cells as if the condition being met? Do I have to
replace (TheDate) with a range or date within the Brackets or replace an
actual 3 letter month for "mmm" like JUL or SEP? What is meant by -1,1? Maybe
I am putting it in wrong. note: I selected every second cell eg: JAN,MAR, MAY
etc....and then I typed in the second formula exactly as you have it then
selected blue as a color. I then selected FEB, APR, JUN etc.....and typed in
the second formula exactly as you had it in the second paragragh and selected
green as a color.

 

[Joerg Mochikun]

I assume that the OP wants first and last days of the month included:
If cells C2:C13 are filled with "Jan"...Dec", then

1) Select range C2:C13
2) From menu select Format => Conditional Formatting
3) For Condition1 select 'Formula is'
4) Type formula
=C2=CHOOSE(MONTH(NOW())-1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
4) Set a colors for this condition
5) Press 'OK'

Now the month before the present month should appear with the selected
color.

Cheers,

Joerg Mochikun

 

[John C]

No. Say for example that in C2, where Jan is, you want it red if the date is
in February. Format the cell red. In C3 where Feb is, you want it blue if the
date is in March. Format the cell blue. In C4 where Mar is, you want it green
if the date is in April. Format the cell green.

Then, in the conditional formatting for the cells, put in the formula. What
will happen is, say the month is April, which means Mar is the one you want
highlighted, when you evaluate the formula, every cell EXCEPT Mar will be
affected by the conditional formatting, because the other 11 months will NOT
be equal, they will have your conditional formatting (of no color fill),
override their normal behaviour of red, blue, etc (everything except green),
leaving ONLY the Mar cell still in it's normal formatting, of green.

 

[Loadmaster]

Joerg, that worked with one color but I would prefer to alternate between
blue and green each month.

 

[Loadmaster]

Yes. thank-you I had to reread the whole thing.

I assume that the OP wants first and last days of the month included:
If cells C2:C13 are filled with "Jan"...Dec", then

1) Select range C2:C13
2) From menu select Format => Conditional Formatting
3) For Condition1 select 'Formula is'
4) Type formula
=C2=CHOOSE(MONTH(NOW())-1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
4) Set a colors for this condition
5) Press 'OK'

Now the month before the present month should appear with the selected
color.

Cheers,

Joerg Mochikun

 

[Joerg Mochikun]

Just set the background fills (colors) for the cells the way you need it and
leave the formula as is.

Cheers,
Joerg Mochikun