Conditional formating for the month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=OR(MONTH(C2)=MONTH(TODAY(),MONTH(C2)+1=MONTH(TODAY())

all in the one CF condition

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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))
 
That wouldn't work for birthdays in the next month, nor would it be
consistent for months with 28, 29, 30 and 31 days.
 
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?
 
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
 
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 Format>Cells>Number. Select GENERAL.
Does the Sample box show 38589? If not then 25aug05 is a text string.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Meeting 12 formatted conditions 9
Conditional formatting 1
Conditional Format Help 2
Formula to show 2 months within 3rd birthday 4
conditional formatting match month 2
Conditional formatting dates 3
Conditional Formatting help 2
Conditional Formatting Applied when Statement Not True 6

Back
Top