Conditional Formatting

I have a formula in Conditional formatting that changes the color of the cell
if a date in that cell is equal to today's date less 2 months and todays date
plus 2 months. This works fine thanks to you guys helping me with the
formula. However, if I change the date in the cell because there is now a new
retest date and the date is more than 2 months, the cell stays red. How can I
get the cell to change to a different color if I put in a new date that does
not meet the criteria.

Thanks again for any help.

Best regards,

Dee

It should work okay. What is the formula, and do you have manual calculation
set?

--

HTH

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


"Dee" <Dee@discussions.microsoft.com> wrote in message
news:B3B4F269-9A99-4D43-B296-C5958910AE0E@microsoft.com...
> I have a formula in Conditional formatting that changes the color of the
cell
> if a date in that cell is equal to today's date less 2 months and todays
date
> plus 2 months. This works fine thanks to you guys helping me with the
> formula. However, if I change the date in the cell because there is now a
new
> retest date and the date is more than 2 months, the cell stays red. How
can I
> get the cell to change to a different color if I put in a new date that
does
> not meet the criteria.
>
> Thanks again for any help.
>
> Best regards,
>
> Dee

Hi Bob,

The formula is:
=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))

If I change the cell to a different retest date that is say 6 months from
today, the cell remains red in color. Should it not change to the default
black?

Thanks very much for your help.

Regards,

Dee

"Bob Phillips" wrote:

> It should work okay. What is the formula, and do you have manual calculation
> set?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Dee" <Dee@discussions.microsoft.com> wrote in message
> news:B3B4F269-9A99-4D43-B296-C5958910AE0E@microsoft.com...
> > I have a formula in Conditional formatting that changes the color of the
> cell
> > if a date in that cell is equal to today's date less 2 months and todays
> date
> > plus 2 months. This works fine thanks to you guys helping me with the
> > formula. However, if I change the date in the cell because there is now a
> new
> > retest date and the date is more than 2 months, the cell stays red. How
> can I
> > get the cell to change to a different color if I put in a new date that
> does
> > not meet the criteria.
> >
> > Thanks again for any help.
> >
> > Best regards,
> >
> > Dee
>
>
>

Dee,

As far as I can see, you are comparing two identical formula, so it will
always resolve to true, so always be red. Just strip it back to one test

=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD
AY()),MONTH(TODAY())+2,DAY(TODAY())))

--

HTH

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


"Dee" <Dee@discussions.microsoft.com> wrote in message
news:3E33C36C-16DF-4076-A83A-0705621ED994@microsoft.com...
> Hi Bob,
>
> The formula is:
>
=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD
AY()),MONTH(TODAY())+2,DAY(TODAY())))=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY
())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))
>
> If I change the cell to a different retest date that is say 6 months from
> today, the cell remains red in color. Should it not change to the default
> black?
>
> Thanks very much for your help.
>
> Regards,
>
> Dee
>
> "Bob Phillips" wrote:
>
> > It should work okay. What is the formula, and do you have manual
calculation
> > set?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Dee" <Dee@discussions.microsoft.com> wrote in message
> > news:B3B4F269-9A99-4D43-B296-C5958910AE0E@microsoft.com...
> > > I have a formula in Conditional formatting that changes the color of
the
> > cell
> > > if a date in that cell is equal to today's date less 2 months and
todays
> > date
> > > plus 2 months. This works fine thanks to you guys helping me with the
> > > formula. However, if I change the date in the cell because there is
now a
> > new
> > > retest date and the date is more than 2 months, the cell stays red.
How
> > can I
> > > get the cell to change to a different color if I put in a new date
that
> > does
> > > not meet the criteria.
> > >
> > > Thanks again for any help.
> > >
> > > Best regards,
> > >
> > > Dee
> >
> >
> >

Dear Bob,

I deleted one instance of the formual and everything is working perfect.
Thank you so much for your help. You are awesome!

Best regards,

Dee

"Bob Phillips" wrote:

> Dee,
>
> As far as I can see, you are comparing two identical formula, so it will
> always resolve to true, so always be red. Just strip it back to one test
>
> =AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD
> AY()),MONTH(TODAY())+2,DAY(TODAY())))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Dee" <Dee@discussions.microsoft.com> wrote in message
> news:3E33C36C-16DF-4076-A83A-0705621ED994@microsoft.com...
> > Hi Bob,
> >
> > The formula is:
> >
> =AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD
> AY()),MONTH(TODAY())+2,DAY(TODAY())))=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY
> ())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))
> >
> > If I change the cell to a different retest date that is say 6 months from
> > today, the cell remains red in color. Should it not change to the default
> > black?
> >
> > Thanks very much for your help.
> >
> > Regards,
> >
> > Dee
> >
> > "Bob Phillips" wrote:
> >
> > > It should work okay. What is the formula, and do you have manual
> calculation
> > > set?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Dee" <Dee@discussions.microsoft.com> wrote in message
> > > news:B3B4F269-9A99-4D43-B296-C5958910AE0E@microsoft.com...
> > > > I have a formula in Conditional formatting that changes the color of
> the
> > > cell
> > > > if a date in that cell is equal to today's date less 2 months and
> todays
> > > date
> > > > plus 2 months. This works fine thanks to you guys helping me with the
> > > > formula. However, if I change the date in the cell because there is
> now a
> > > new
> > > > retest date and the date is more than 2 months, the cell stays red.
> How
> > > can I
> > > > get the cell to change to a different color if I put in a new date
> that
> > > does
> > > > not meet the criteria.
> > > >
> > > > Thanks again for any help.
> > > >
> > > > Best regards,
> > > >
> > > > Dee
> > >
> > >
> > >
>
>
>

Always helps when we can see the formula :-))

Glad it worked.

Regards

Bob


"Dee" <Dee@discussions.microsoft.com> wrote in message
news:DE99D9C3-281B-4D89-B71C-BF6A182C003C@microsoft.com...
> Dear Bob,
>
> I deleted one instance of the formual and everything is working perfect.
> Thank you so much for your help. You are awesome!
>
> Best regards,
>
> Dee
>
> "Bob Phillips" wrote:
>
> > Dee,
> >
> > As far as I can see, you are comparing two identical formula, so it will
> > always resolve to true, so always be red. Just strip it back to one test
> >
> >
=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD
> > AY()),MONTH(TODAY())+2,DAY(TODAY())))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Dee" <Dee@discussions.microsoft.com> wrote in message
> > news:3E33C36C-16DF-4076-A83A-0705621ED994@microsoft.com...
> > > Hi Bob,
> > >
> > > The formula is:
> > >
> >
=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD
> >
AY()),MONTH(TODAY())+2,DAY(TODAY())))=AND(F3>=DATE(YEAR(TODAY()),MONTH(TODAY
> >
())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))
> > >
> > > If I change the cell to a different retest date that is say 6 months
from
> > > today, the cell remains red in color. Should it not change to the
default
> > > black?
> > >
> > > Thanks very much for your help.
> > >
> > > Regards,
> > >
> > > Dee
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > It should work okay. What is the formula, and do you have manual
> > calculation
> > > > set?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Dee" <Dee@discussions.microsoft.com> wrote in message
> > > > news:B3B4F269-9A99-4D43-B296-C5958910AE0E@microsoft.com...
> > > > > I have a formula in Conditional formatting that changes the color
of
> > the
> > > > cell
> > > > > if a date in that cell is equal to today's date less 2 months and
> > todays
> > > > date
> > > > > plus 2 months. This works fine thanks to you guys helping me with
the
> > > > > formula. However, if I change the date in the cell because there
is
> > now a
> > > > new
> > > > > retest date and the date is more than 2 months, the cell stays
red.
> > How
> > > > can I
> > > > > get the cell to change to a different color if I put in a new date
> > that
> > > > does
> > > > > not meet the criteria.
> > > > >
> > > > > Thanks again for any help.
> > > > >
> > > > > Best regards,
> > > > >
> > > > > Dee
> > > >
> > > >
> > > >
> >
> >
> >