conditional formatting based on month as textClosed

Question

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)

venkat1926 · Answer

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.

sh4dow · Answer

Currently the months are entered in as 'November' and formatted as date, so the cell shows 'November' only.

venkat1926 · Answer

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

sh4dow · Answer

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?

venkat1926 · Answer

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

sh4dow · Answer

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...

venkat1926 · Answer

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

sh4dow · Answer

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.

venkat1926 · Answer

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.

Conditional formatting based on month as text

9 responses

Similar discussions

Subscribe To Our Newsletter!