Conditional formatting based on month as text [Closed]

- - Latest reply: venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
- Nov 15, 2010 at 08:37 PM
Hi,

I have a list of months in one column, and I want to format it so that if it is the current month, the cell is red; if the month is one month or more behind, it's grey, and if it's the following month, it's yellow.


I'd also like the month to be displayed as text, rather than in date format.

Is this possible?

I don't know enough about formulae to write, or understand them, so explanations for each section would be very helpful so I can tweak as needed!!

Many thanks!

EDIT:

=Q2=TEXT(TODAY(),"mmmm")
Ok so I found this formula for highlighting whichever month is todays date's month (so currently all November cells are highlighted)
I can't seem to edit this to format if the month is +1 of the current month (so December), or < the current month (so everything before November)
See more 

9 replies

Best answer
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
770
1
Thank you
as I told you, you cannot type "November" in a cell and ask excel to recognize October as one month before November,. what you have to do is to enter dates and not texts
in my regional configuration I should enter dates as month/day/year
if so enter like this

in A1 enter 1/1/10
highlight some about 12 cells from A1 down
click edit-fill-series
in series window check "month" under data unit
make step value as 1 (it is already there)
click OK.
now the entry will be

if you are interested you can print out these instructions and follow.

1/1/2010
2/1/2010
3/1/2010
4/1/2010
5/1/2010
6/1/2010
7/1/2010
8/1/2010
9/1/2010
10/1/2010
11/1/2010
12/1/2010

keeping the highlight over these cells
click format menu, cells, custom
in the format cells window type mmmm
and click OK

the data will now be

January
February
March
April
May
June
July
August
September
October
November
December


now only excel will recognize arithmetic of dates


now select A1
click format-conditional formatting
under condition 1 click the triangle(or small arrow) and choose
the formula is
hit F2
in the right window type
=MONTH(A1)=MONTH(TODAY())
click format below this line and choose pattern(last item)
and choose the color red

now click "add" at the bottom
second condition 1 comes up
now repeat the action except the formula on the right window will be

=MONTH(A1)<MONTH(TODAY())
and choose pattern color grey
again click add
repeat except the formula is
=MONTH(A1)>MONTH(TODAY())
and choose pattern color as yellow

now click ok
again click ok

the color pattern may be changed if you want

now select A1 click edit-copy
select A1 to A12 and click edit-pastespecial-FORMAT
CLICK OK

sorry this is a cumbersome affair but you will learn how to do condition formatting. remember in excel 2003 and before you cannot have more than 3 conditions.
if the months go to the next year the formulas need some modification

of course instead of this you can have a macro. I am sure you are familiar with
macro . in that case post back

If I come up with some simpler solution I shall let you know

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 1939 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
770
0
Thank you
how did you enter the months as "text" or as dates. for e.g. you can type 1/1/10
and format it as Jan.
or
you can type the texts jan in a cell then it will be text and you cannot do any calculation

it will be more helpful if VERY SMALL extract of data sheet is posted.
0
Thank you
Currently the months are entered in as 'November' and formatted as date, so the cell shows 'November' only.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
770
0
Thank you
I am wondering how you can type "November" in a cell and format it as date.
November is a text and date is an integer. see what is seen in the formula bar, next to fx at the top
0
Thank you
the Formular bar has the word 'November' and the drop-down box for format has 'date' (I'm in excel 07 btw if that helps).

I don't know much about the more complicated aspects of excel though, so I'm not saying what I have is correct, or works :)

The formular =Q2=TEXT(TODAY(),"mmmm") works perfectly for the current month - is there a way to expand this for future and previous months? Like if all the dates I have are all formatted as text, and it compares that to the text of last month/next month?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
770
0
Thank you
sorry it is not clear what you want

type in A1 this formula
=TEXT(TODAY()+28*(COLUMN(A1)-1),"mmmm")
copy A1 across the row over the columns .
It will work to some extent
0
Thank you
I have a column, where I enter in a month something needs to be completed(there is no specific date, just a month).

So eg. Col A would be like:
September
November
October
November
November
December
November
December
December


I need it to highlight red the current month based on todays date (so right now, all the 'November' cells would be highlighted red)

All cells containing a month earlier than the current month in grey (so currently October,September,August etc)

The month following the current month, in yellow (So December)



The only part I have working, is for the current month:
=Q2=TEXT(TODAY(),"mmmm")
This currently highlights anything with 'November' in it.

I need the formula for previous months, and the next following month.




Does that explain better? I'm sorry I can't upload any file to demonstrate...
0
Thank you
Ok.. So I've now got my months showing as dates in the formula bar, and just as months in the cells like you've advised. That's working fine.

But the cond.formatting simply isn't.
I'm using excel 2007, so the cond.format option is on the toolbar under styles. On clicking this, and choosing 'new rules', there isn't any 'condition 1' or 'add' condition - I can only add the formula to one rule, and then have to create a 2nd seperate rule, for the 2nd condition etc.

I've tried adding the three rules and dragging it down over the cells (format only), but it is completely random as to which cells are which colour.


I am not familiar with macro's I'm afraid, I am an excell noob (which I'm sure you've noticed by now :) )


I do greatly appreciate your time and help though.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
770
0
Thank you
In excel 2007 you have every time call conditional formatting window and click new rules and type the new formula (there is no add in 2007). in 2007 you can have 64 conditions.

study completely the help for this in excel 2007.