Conditional Formatting with Dates (Revised)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BillXMachina - 16 May 2008 17:02 GMT

Sorry please ignore my previous posting.
I hope someone will be able to help me. I have tried a number of things but
can't get this just right. I want to be able to make the text in a cell turn
red if todays date is between two dates. Example: in Cells A1 through A12
are the months of the year (January, February, March, April, May, June, July,
August, September, October, November, December). I want to highlight the
current month in Red.

I have tried the following and had mixed results. In each cell I enter the
following formula with the appropriate dates for that month. The examples
below are for cell A1 (January):

Assume today is January 15th. The text in cell A1 should turn red while A2
through A12 stay black.

1. =OR(TODAY() >= 1-1-2008, TODAY() <= 1-31-2008)
Results = Turns text red regardless of current date

2. =AND(TODAY() >= 1-1-2008, TODAY() <= 1-31-2008)
Results = Does not turn text red regardless of current date

I'm not sure whats wrong here, please help.

Thanks,
Bill

Reply to this Message

Roger Govier - 16 May 2008 17:25 GMT

Hi Bill

Try
=MONTH(TODAY())=MONTH($A1)

Signature

Regards
Roger Govier

> Sorry please ignore my previous posting.
> I hope someone will be able to help me. I have tried a number of things
[quoted text clipped - 27 lines]
> Thanks,
> Bill

Reply to this Message

BillXMachina - 16 May 2008 17:57 GMT

Hi Roger and ND Pard

I tried both of these solutions and neither one works. I don't understand
what the problem might be. I put the formulas into the cells for each month
and then changed the date on the computer and nothing changes.

Hi Bill

Try
=MONTH(TODAY())=MONTH($A1)

Signature

Regards
Roger Govier

Sorry please ignore my previous posting.
I hope someone will be able to help me. I have tried a number of things
but
can't get this just right. I want to be able to make the text in a cell
turn
red if todays date is between two dates. Example: in Cells A1 through
A12
are the months of the year (January, February, March, April, May, June,
July,
August, September, October, November, December). I want to highlight the
current month in Red.

I have tried the following and had mixed results. In each cell I enter
the
following formula with the appropriate dates for that month. The examples
below are for cell A1 (January):

Assume today is January 15th. The text in cell A1 should turn red while
A2
through A12 stay black.

1. =OR(TODAY() >= 1-1-2008, TODAY() <= 1-31-2008)
Results = Turns text red regardless of current date

2. =AND(TODAY() >= 1-1-2008, TODAY() <= 1-31-2008)
Results = Does not turn text red regardless of current date

I'm not sure whats wrong here, please help.

Thanks,
Bill

Reply to this Message

Roger Govier - 16 May 2008 18:23 GMT

Hi Bill

It looks as though you may have text values in a1:A12
Enter 01/01/2008 in A1 and use the fill handle to copy down to A12.
It should then show 02/01/2008, 03/01/2008 etc.
Select cells a1A12>Format>cells>Custom> mmmm

You should now see January, February etc displayed, but the underlying
values will be true Excel dates and the comparison should work

Signature

Regards
Roger Govier

> Hi Roger and ND Pard
>
[quoted text clipped - 39 lines]
> Thanks,
> Bill

Reply to this Message

BillXMachina - 16 May 2008 19:29 GMT

Roger,

Thank you, that did it! I appreciate the help.

Thanks,
Bill

> Hi Bill
>
[quoted text clipped - 49 lines]
> > Thanks,
> > Bill

Reply to this Message

ND Pard - 16 May 2008 17:26 GMT

Assumming cells A1 throught A12 are TEXT, try a formula similar to:

=$A1=TEXT(TODAY(),"MMMM")

Good Luck.

> Sorry please ignore my previous posting.
> I hope someone will be able to help me. I have tried a number of things but
[quoted text clipped - 21 lines]
> Thanks,
> Bill

Reply to this Message