Conditional formating for the month
 

I have a column with the birthday of some clients and I want the cell format
to change to yellow when his date of birth is withing the current and the
next monat. In order to do that I wrote these formula in the first and
second field of the conditional formating panel=C2=MONTH(TODAY()) and
=C2=MONTH(TODAY())+1 , but it doesn't work.

Can't you tell me what am I doing wrong

Thanks

Try this, using only one condition:
=OR(MONTH(C2)=MONTH(NOW()),MONTH(C2)=MONTH(NOW())+ 1)

=OR(MONTH(C2)=MONTH(TODAY(),MONTH(C2)+1=MONTH(TODA Y())

all in the one CF condition

--

HTH

RP
(remove nothere from the email address if mailing direct)


"viddom" wrote in message
...
I have a column with the birthday of some clients and I want the cell
format
to change to yellow when his date of birth is withing the current and the
next monat. In order to do that I wrote these formula in the first and
second field of the conditional formating panel=C2=MONTH(TODAY()) and
=C2=MONTH(TODAY())+1 , but it doesn't work.

Can't you tell me what am I doing wrong

Thanks

Hi!

Try this:

=OR(MONTH(C2)=MONTH(TODAY()),MONTH(TODAY())-1=MONTH(C2))

Biff

"viddom" wrote in message
...
I have a column with the birthday of some clients and I want the cell
format
to change to yellow when his date of birth is withing the current and the
next monat. In order to do that I wrote these formula in the first and
second field of the conditional formating panel=C2=MONTH(TODAY()) and
=C2=MONTH(TODAY())+1 , but it doesn't work.

Can't you tell me what am I doing wrong

Thanks

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

That formula (with the correct parens) will highlight birthdays in the
current and last month, not the current and next month. In addition, it
won't work for the end of the year rollover of MONTH().

This should:

=OR(MONTH(C2)=MONTH(TODAY()),MONTH(C2)=(MOD(MONTH( TODAY()),12)+1))

In article ,
"Bob Phillips" wrote:

=OR(MONTH(C2)=MONTH(TODAY(),MONTH(C2)+1=MONTH(TODA Y())

all in the one CF condition

That wouldn't work for birthdays in the next month, nor would it be
consistent for months with 28, 29, 30 and 31 days.

In article .com,
"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the cell do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

Mr JE McGimpsey,

do you know the formula, cause I tried all the others and it didn't work,
maybe I am missing some step

"JE McGimpsey" wrote:

That wouldn't work for birthdays in the next month, nor would it be
consistent for months with 28, 29, 30 and 31 days.

In article .com,
"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

Are you sure that 25aug05 is being recognized as a TRUE EXCEL DATE?

It looks like a TEXT string to me!

In my version of Excel if I type in 25aug05, Excel converts that string to
25-Aug-05 which is a TRUE EXCEL DATE.

Select the "date" cell and then goto FormatCellsNumber. Select GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

Biff

"viddom" wrote in message
...
Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the cell
do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

Dear Biff,

my computer do the same, but still the cell format do not change. If I have
to generate some code, lI will be pleased to have some example

"Biff" wrote:

Are you sure that 25aug05 is being recognized as a TRUE EXCEL DATE?

It looks like a TEXT string to me!

In my version of Excel if I type in 25aug05, Excel converts that string to
25-Aug-05 which is a TRUE EXCEL DATE.

Select the "date" cell and then goto FormatCellsNumber. Select GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

Biff

"viddom" wrote in message
...
Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the cell
do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

Try this:

If you date cell is C2 and it contains the true Excel date of 25-Aug-05, in
a cell next to that enter this formula:

=OR(MONTH(C2)=MONTH(TODAY()),MONTH(TODAY())-1=MONTH(C2))

What result do you get?

Biff

"viddom" wrote in message
...
Dear Biff,

my computer do the same, but still the cell format do not change. If I
have
to generate some code, lI will be pleased to have some example

"Biff" wrote:

Are you sure that 25aug05 is being recognized as a TRUE EXCEL DATE?

It looks like a TEXT string to me!

In my version of Excel if I type in 25aug05, Excel converts that string
to
25-Aug-05 which is a TRUE EXCEL DATE.

Select the "date" cell and then goto FormatCellsNumber. Select GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

Biff

"viddom" wrote in message
...
Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the
cell
do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

No results, the format do not change, some other idea?

"Biff" wrote:

Try this:

If you date cell is C2 and it contains the true Excel date of 25-Aug-05, in
a cell next to that enter this formula:

=OR(MONTH(C2)=MONTH(TODAY()),MONTH(TODAY())-1=MONTH(C2))

What result do you get?

Biff

"viddom" wrote in message
...
Dear Biff,

my computer do the same, but still the cell format do not change. If I
have
to generate some code, lI will be pleased to have some example

"Biff" wrote:

Are you sure that 25aug05 is being recognized as a TRUE EXCEL DATE?

It looks like a TEXT string to me!

In my version of Excel if I type in 25aug05, Excel converts that string
to
25-Aug-05 which is a TRUE EXCEL DATE.

Select the "date" cell and then goto FormatCellsNumber. Select GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

Biff

"viddom" wrote in message
...
Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the
cell
do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

I have to go, I hope we find a solution tomorrow.

Thanks

"viddom" wrote:

No results, the format do not change, some other idea?

"Biff" wrote:

Try this:

If you date cell is C2 and it contains the true Excel date of 25-Aug-05, in
a cell next to that enter this formula:

=OR(MONTH(C2)=MONTH(TODAY()),MONTH(TODAY())-1=MONTH(C2))

What result do you get?

Biff

"viddom" wrote in message
...
Dear Biff,

my computer do the same, but still the cell format do not change. If I
have
to generate some code, lI will be pleased to have some example

"Biff" wrote:

Are you sure that 25aug05 is being recognized as a TRUE EXCEL DATE?

It looks like a TEXT string to me!

In my version of Excel if I type in 25aug05, Excel converts that string
to
25-Aug-05 which is a TRUE EXCEL DATE.

Select the "date" cell and then goto FormatCellsNumber. Select GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

Biff

"viddom" wrote in message
...
Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the
cell
do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

The one that I posted works fine for me.

In article ,
viddom wrote:

do you know the formula, cause I tried all the others and it didn't work,
maybe I am missing some step

No results, the format do not change, some other idea?

You didn't do what I asked!

I want you to enter that formula into a cell on the worksheet. You'll get
either TRUE or FALSE.

If you get TRUE that means the formula works. If you get #VALUE! that means
C2 is not a true Excel date and is a text string. You should not get FALSE!

Biff

"viddom" wrote in message
...
No results, the format do not change, some other idea?

"Biff" wrote:

Try this:

If you date cell is C2 and it contains the true Excel date of 25-Aug-05,
in
a cell next to that enter this formula:

=OR(MONTH(C2)=MONTH(TODAY()),MONTH(TODAY())-1=MONTH(C2))

What result do you get?

Biff

"viddom" wrote in message
...
Dear Biff,

my computer do the same, but still the cell format do not change. If I
have
to generate some code, lI will be pleased to have some example

"Biff" wrote:

Are you sure that 25aug05 is being recognized as a TRUE EXCEL DATE?

It looks like a TEXT string to me!

In my version of Excel if I type in 25aug05, Excel converts that
string
to
25-Aug-05 which is a TRUE EXCEL DATE.

Select the "date" cell and then goto FormatCellsNumber. Select
GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

Biff

"viddom" wrote in message
...
Guys, I don't know what I am doing wrong,cause I tried the
conditional
formatign with each formula, but when I put a date like 25aug05 the
cell
do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and
"=today()+30"
Format the cell to be color = "yellow"

If those text dates are all in one column, you can convert them by:

Select the range (whole column???)
data|text to columns
delimited
(uncheck everything)
choose Date (dmy)
and finish up



viddom wrote:

Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the cell do
not change a format. What step I am missing?

"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"



--

Dave Peterson

Hi,

I am here again, still with the same problem. And I would like to know if
you tried the code you gave me. Hope you and the guys can help me

"viddom" wrote:

Dear Biff,

my computer do the same, but still the cell format do not change. If I have
to generate some code, lI will be pleased to have some example

"Biff" wrote:

Are you sure that 25aug05 is being recognized as a TRUE EXCEL DATE?

It looks like a TEXT string to me!

In my version of Excel if I type in 25aug05, Excel converts that string to
25-Aug-05 which is a TRUE EXCEL DATE.

Select the "date" cell and then goto FormatCellsNumber. Select GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

Biff

"viddom" wrote in message
...
Guys, I don't know what I am doing wrong,cause I tried the conditional
formatign with each formula, but when I put a date like 25aug05 the cell
do
not change a format. What step I am missing?


"cgarrett1999" wrote:

This seems to work also.

Position the cursor over the cells where the date is.

Conditional formatting,
Condition1 "Cell Value Is" "between" "=today()" and "=today()+30"
Format the cell to be color = "yellow"

Hi,

Perhaps try this one

Condition 1: select Formula is

In the next panel type the following if the date colume is "F"
=AND($F2=TODAY(),$F2<TODAY()+30)

Frances


"viddom" wrote:

I have a column with the birthday of some clients and I want the cell format
to change to yellow when his date of birth is withing the current and the
next monat. In order to do that I wrote these formula in the first and
second field of the conditional formating panel=C2=MONTH(TODAY()) and
=C2=MONTH(TODAY())+1 , but it doesn't work.

Can't you tell me what am I doing wrong

Thanks

What did you get when you tried the solution I proposed?

In article ,
viddom wrote:

I am here again, still with the same problem