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!!
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)
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
in series window check "month" under data unit
make step value as 1 (it is already there)
now the entry will be
if you are interested you can print out these instructions and follow.
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
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
in the right window type
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
and choose pattern color grey
again click add
repeat except the formula is
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
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
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?
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.