Conditional Formatting

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