Conditional Formatting for a certain month

[Advo]

Having a bit of an issue at the moment, ive got the Cell for instance
A1
Which contains

04-Jan-06

Basically Im after some conditional formatting to say if the month is
January, then make the cell blue.

Which will then lead to me making the other cells different colours
depending on their month, but again, it has to be in this format
(DD-MM-YY)

Thanks for any help, muchly apreciated

 

[Don Guillett]

Assuming a properly formatted date, formula is
=if(month(a1)=1)

 

[Advo]

Hey, thanks for the help, i tried that though and it says the formula
contains an error.

This is the exact copy and paste of the date im testing with:

04-Jan-06

so tried:

=if(month(I17)=1)

and

=if(month(I17)=JAN) and =if(month(I17)='JAN')

anymore ideas please?

 

[Don Guillett]

try it this way in cell i17 assuming a properly formatted date in i17
=IF(MONTH(i17)=1,1,0)

 

[Advo]

Apologies but still having issues, used:

=IF(MONTH(I17)=1,1,0) in conditional formatting and the dates
currently in my column are:

04-Aug-06
02-Jan-05
01-Feb-02
04-Jan-06


and the conditional format highlighted them all :s

 

[Bob Phillips]

In CF, change Condition 1 to Formula Is, and just use the formula

=MONTH(I17)=1

assuming the dates start in I17

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Advo]

Hey, i tried that, it hightlights the month of Jan, but also other
months too :s

 

[Pete_UK]

Note that all the formulae are looking at I17, whereas in your first
post you referred to A1 - are you sure you are referencing the correct
cell? Sometimes Excel puts quotes around the formula - go back in and
delete these if necessary. Try to get the correct effect on just one
cell, then you can use the Format Painter to apply it to other cells.

Hope this helps.

Pete

 

[Bob Phillips]

Maybe blanks?

Try

=AND(I17<>"",MONTH(I17)=1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)