How to use months in formula.

D

Dave Dobson

A column has different months entered. Generally these will be a month
either side of the current month, or the current month itself. I would like
to use conditional formatting to change the cell colour to:

Green - Next month
Amber - Current month
Red - Last month and beyond.

How can I do this without specifying +/- number of days, as there are a
different number of days in each month?
 
M

Max

Assuming real dates in col A,

Select col A (with A1 active), then apply the CF's formulas / fills:

Condition 1
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())-1,A1<>"")
Format: Red

Condition 2
Formula is:
=AND(MONTH(A1)=MONTH(TODAY()),A1<>"")
Format: Amber

Condition 3
Formula is:
=AND(MONTH(A1)=MONTH(TODAY())+1,A1<>"")
Format: Green
 
D

Dave Dobson

Ok, that worked for a specific cell with a real date.

What if I wanted to apply this to a whole column? My column has a header in
A1 and the column will be increased/decreased on a daily basis.

eg: Today there is data in A2:A52. Tomorrow there may be data in A2:A58.
Next day may only be A2:A46 etc.

The data is not a real date. "September" will be written in that cell. I
could, however, enter a real date.
 
R

Roger Govier

Hi

Yes, you can apply the CF to as many cells in the column as you wish.

Where you have September, enter 01/09/2006 (or your Regional format for
01 Sep 2006) and then format that cell
Format>Cells>Number>Custom> mmmm
and what will be displayed is September.
 
M

Max

Dave Dobson said:
Ok, that worked for a specific cell with a real date.
What if I wanted to apply this to a whole column? ..
Click to expand...

If you had selected the entire col A before applying the CF as suggested in
my earlier response, re line:it would have worked for the entire col A ..
 
D

Dave Dobson

Ok. The cell above the one just entered now changes colour.

You say "Select col A": for this I clicked on 'A' to highlight the whole
column. Did I misinterpret? Please also verify what you mean by 'Active'.
How can a cell be active if the whole column is highlighted?

Thanks.
 
M

Max

If you selected the entire col A by clicking on the col header "A", then
it's okay, A1 will be active cell (the highlighted "white" cell within the
selection). But one could have also selected col A by selecting A65536
first, then do a CTRL+SHIFT+Arrow Up, in which case A65536 would be the
active cell. The earlier cond format formulas given were to be applied to
col A with A1 active, not with A65536 active. Hope this clarifies.

Were you able to get the CF applied to the entire col A?
I'm still not sure from your response.
 
R

Roger Govier

Hi Dave

If you click on the column header letter to select the whole column, the
active cell is automatically the first cell in that column.

If you clicked on say cell A4, then held your left mouse button down as
you proceeded down the column, on release of the mouse button, you would
have marked a range of cells, but A4 would be the active cell

Are you using the date entered in Cell A1 as your comparative date,
rather than Today() ?
If so, you will need to amend each of Max's formulae, but ensure that
you use an absolute cell reference for the second occurrence of A1 in
the formulae.

=AND(MONTH(A1)=MONTH($A$1)-1,A1<>"")

This will make Cell A1 itself, Amber.
If you don't want that, having formatted the whole column, just mark
cell A1 and remove conditional formatting.
 
D

Dave Dobson

Thank you. Yes, figured it out at last.

I had used column 'A' in this dialogue just as an example. The actual
column on my sheet is 'I'. Just realised that in my actual column A there
are dates aswell!

All works fine now. If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date, how could I change the formula to
accomodate this?

This perhaps is not as important. I am glad my original problem is now
solved.

Thanks again.
 
M

Max

.. All works fine now.

Glad to hear that said:
If, for example, a month needed to be entered that was
beyond the +/- 1 month from today's date,
how could I change the formula to accomodate this?
Click to expand...

Sounds like you want to implement another 2 trigger conditions?

CF allows 3 trigger conditions to format cells, with an implicit 4th (ie
non-trigger condition) being the default format.

You might want to check out Bob Phillips'
CFPlus - Extended Conditional Formatter:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

Believe it can handle, to quote:
Multiple Conditional Formats, up to 30 in total for any range of cells
 
M

Max

Dave,

Upon further thoughts re-visiting your Q:
Perhaps these revised cond format formulas would do it better ..

Condition 1:
=AND(DATE(YEAR(A1),MONTH(A1),1)<=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),A1<>"")
Format: Red fill

Condition 2:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY()),MONTH(TODAY()),1),A1<>"")
Format: Amber fill

Condition 3:
=AND(DATE(YEAR(A1),MONTH(A1),1)>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),A1<>"")
Format: Green fill
 
D

Dave Dobson

Where the first formula had -1, which would show red if the month entered was
1 month less then today and the third formula had +1, would show green if the
month entered was 1 month more than today. That works great if today is
October and either September or November are entered. If August or December
are entered, then there is no colour change as these months are outside what
has been specified in the formula.

Could the formula read '-1 or less' and '+1 or more' without creating a
fourth or fifth condition?

If not, I will investigate multiple trigger conditions as suggested.

Once again, thank you.
 
M

Max

Could the formula read '-1 or less' and '+1 or more' without creating a
fourth or fifth condition?
Click to expand...

Dave, posted this further response earlier (you might have missed it)

Upon further thoughts re-visiting your Q:
Perhaps these revised cond format formulas would do it better ..

Condition 1
=AND(DATE(YEAR(A1),MONTH(A1),1)<=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),A1<>"")
Format: Red fill

Condition 2:
=AND(DATE(YEAR(A1),MONTH(A1),1)=DATE(YEAR(TODAY()),MONTH(TODAY()),1),A1<>"")
Format: Amber fill

Condition 3
=AND(DATE(YEAR(A1),MONTH(A1),1)>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),A1<>"")
Format: Green fill

The above should do it for you ..
 
D

Dave Dobson

Wow, that is spot on!

As this is a shared workbook, will the fact that it is shared prevent
anybody else from altering this CF?
 
Top